How to get smallest result from multi-criteria index/match function

Question

I have a sheet with transactions. Each txn has an airport and an amount of fuel pumped. I have a second sheet with a list of locations, each row of which has min and max values for fuel bands (e.g, 1-500, 501-1000, min/max stored in separate columns), and each each of which have a price (for fuel per gallon).

I need to use the values per row in the first spreadsheet to search the second spreadsheet for a match on airport (ICAO code), greater than bottom of band, and less than top of band, and then return the unit price of fuel. The catch is that I can have multiple matches, and I need the smallest/lowest value.

I have some familiarity with Index/Match multi criteria arrays. So, I wrote the following and tried it:

=INDEX(FuelPrices!$D$2:$D$3398,MATCH(1,(FuelPrices!A:A=H2)*(FuelPrices!B:B>=N2)*(FuelPrices!C:C<=N2),0))

Where "Fuel" is my first sheet and "FuelPrices" is the sheet I'm looking up the values in. No matter WHAT I do it throws an #NA error. So, I figured maybe the problem was I was returning an array? I tried this:

=INDEX(FuelPrices!$D$2:$D$3398,SMALL(MATCH(1,(FuelPrices!A:A=H2)*(FuelPrices!B:B>=N2)*(FuelPrices!C:C<=N2),0),1))

Figuring it would give me the smallest value from the returned array. No go. I've tried some other tricks (using another Index function around the match) and nothing seems to work.

Basically I just want to get the function to return the lowest matching value for the provided criteria.

There should be an asterisk here (FuelPrices!A:A=H2)*(FuelPrices!B:B>=N2)*(FuelPrices!C:C<=N2) is the first thing I've noticed, probably should be entered as an array formula, but will have more of a look at it later. — Tom Sharpe
Ah, sorry, the asterisk got processed out by the editor. I DID have asterisks in the function to multiply the matches, and I entered it as an array when I saved it, but it still didn't work. Just updated my original post to show the formulas as code (retaining the asterisks). Thanks! — Rick Tilghman
No probs. From your sample data it looks as though you could only have one match, but will add to my answer if you can definitely have more than one match and want to find the smallest. — Tom Sharpe

Tom Sharpe Tom Sharpe · Accepted Answer · 2019-06-25T07:10:22

The short answer is that the < and > are the wrong way round. This does give an answer

=INDEX(FuelPrices!$D$2:$D$10,MATCH(1,(FuelPrices!$A$2:$A$10=H2)*(FuelPrices!$B$2:$B$10<=N2)*(FuelPrices!$C$2:$C$10>=N2),0))

if entered as an array formula using CtrlShiftEnter

I have changed all ranges to a small number of rows for testing purposes.

FuelPrices sheet

Fuel sheet

If you do want to find the smallest subject to the same conditions, you don't need index but can use small (or min)

=SMALL(IF((FuelPrices!$A$2:$A$10=H2)*(FuelPrices!$B$2:$B$10<=N2)*(FuelPrices!$C$2:$C$10>=N2),FuelPrices!$D$2:$D$10),1)

Or if you prefer you can use Aggregate (non-array formula)

=AGGREGATE(15,6,FuelPrices!$D$2:$D$10/((FuelPrices!$A$2:$A$10=H2)*(FuelPrices!$B$2:$B$10<=N2)*(FuelPrices!$C$2:$C$10>=N2)),1)

How to get smallest result from multi-criteria index/match function

1 Answers