Help needed looking up two criteria in a table and returning a result

Copper Contributor

Hi

 

I'm sure this probably really easy but I can't figure it out.

 

I have a look up table that has a column containing a cost band, then for each cost band, a range of minimum and maximum amounts. For each combination of cost band and a particular min/max amount range there is a percentage which is the return value.

 

My data consists, for each entry, of a cost band value, then an amount, so I want to look up in the table the correct row based on the cost band and where the amount falls in the min/max range, and return the relevant percentage.

 

I have attached a workbook showing the above.

 

Can anyone help supply the formula I need to use to achieve this please? Any questions please ask.

 

Thanks in advance.

2 Replies

@JeremyNoles 

That could be

=LOOKUP(I3,1/($A$3:$A$30=H3)*$B$3:$B$30,$D$3:$D$30)

image.png

@JeremyNoles 

 

OR you may try one of these formulas also to get the desired output.

 

In J3

=SUMIFS($D$3:$D$30,$A$3:$A$30,H3,$B$3:$B$30,"<="&I3,$C$3:$C$30,">="&I3)

and then copy it down.

 

Or in J3

=INDEX($D$3:$D$30,MATCH(1,INDEX(($A$3:$A$30=H3)*($B$3:$B$30<=I3)*($C$3:$C$30>=I3),),0))