Nov 19 2020 03:20 AM
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.
Nov 19 2020 03:50 AM
Nov 19 2020 04:05 AM
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))