# 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

• 13.8 KB Views: 7
Last edited:

#### Pierre

##### Member
Small precision, when I say top 3, it means from cheaper price to most expensive

#### AlanSidman

##### Well-Known Member
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

• 35.5 KB Views: 5

#### Peter Bartholomew

##### Well-Known Member
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) )

#### rahulshewale1

##### Active Member
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

• 30.5 KB Views: 1

#### AlanSidman

##### Well-Known Member
Three workable solutions and no OP response. mmmm!

#### AliGW

##### Active Member
The forum equivalent of a smash and grab ... All too familiar, sadly.