• 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


  • 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



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 :)



Last edited:


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.


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) )


Active Member
hi @Pierre ,

Formula Solution.

C2 Down:

D2 Down.