• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find top 3 from matrix with criteria

Pierre

Member
Hello,

I have a transport matrix with give me transport cost to deliver different number of pallets to different area codes for several transport company.
In a separate sheet, I would like to have formulas which would give me the top 3 with company name and associated price, when the user enter the area code and the number of pallets.
I'm guessing I should use array formulas? I'm a bit stuck with the fact that I have one vertical criteria and one horizontal criteria...
Any help would be much appreciated :)

Pierre
 

Attachments

  • Transport_matrix.xlsx
    13.8 KB · Views: 8
Last edited:
I created a parameter query in Power Query. You select the area and the number of pallets in combo boxes. Click on the Refresh button and go to Sheet 1 which lists your bottom 3 prices. The file is attached for your review.
 

Attachments

  • transport.xlsm
    35.5 KB · Views: 7
Using MS365 dynamic array functions
= SORT( FILTER( IF({1,0}, company, XLOOKUP(number, pallets, priceTable) ), area=code ), 2 )

Or, arranging the formula using the beta release function LET:
= LET(
volumeData, IF({1,0}, company, XLOOKUP(number, pallets, priceTable) ),
tarifs, FILTER(volumeData, area=code ),
SORT(tarifs, 2) )
 
hi @Pierre ,

Formula Solution.

C2 Down:
=IFERROR(INDEX(Matrix!$B$2:$B$49,AGGREGATE(15,6,ROW(Matrix!$D$2:$D$49)-ROW(Matrix!D$2)+1/(Matrix!$A$2:$A$49=Feuil1!$C$2),ROWS(Feuil1!$B$6:$B6))),"")

D2 Down.
=IFERROR(INDEX(INDEX(Matrix!$C$2:$L$49,,MATCH(Feuil1!$C$3,Matrix!$C$1:$L$1,0)),AGGREGATE(15,6,ROW(Matrix!$D$2:$D$49)-ROW(Matrix!D$2)+1/(Matrix!$A$2:$A$49=Feuil1!$C$2),ROWS(Feuil1!$B$6:$B6))),"")
 

Attachments

  • transport.xlsm
    30.5 KB · Views: 2
Back
Top