Index/Match - Multiple Column Lookup

[Joelsephaz]

Hey All,

Been a long while since I've asked for help but I've got something I'm stuck on.

I'm trying to write a Index Match formula using two criteria. This first is a rate input to match against Column A and the second is a % input to (non-exact <) match against values in column B:H, and I'd like to return to text in the column header.

	Black	Grey	Red	Orange	Yellow	Green	Blue
1	22.00%	24.00%	26.00%	28.00%	32.00%	34.00%	36.00%
2	21.60%	23.60%	25.60%	27.60%	31.60%	33.60%	35.60%
3	21.20%	23.20%	25.20%	27.20%	31.20%	33.20%	35.20%
4	20.80%	22.80%	24.80%	26.80%	30.80%	32.80%	34.80%
5	20.40%	22.40%	24.40%	26.40%	30.40%	32.40%	34.40%
6	20.00%	22.00%	24.00%	26.00%	30.00%	32.00%	34.00%
7	19.60%	21.60%	23.60%	25.60%	29.60%	31.60%	33.60%
8	19.20%	21.20%	23.20%	25.20%	29.20%	31.20%	33.20%
9	18.80%	20.80%	22.80%	24.80%	28.80%	30.80%	32.80%
10	18.40%	20.40%	22.40%	24.40%	28.40%	30.40%	32.40%
11	18.00%	20.00%	22.00%	24.00%	28.00%	30.00%	32.00%
Criteria 1:	Rate	5
Criteria 2:	% bucket	24.00%
Expected Result:	Red

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

 

[lrobbo314]

So I put your table in range A1:H12. Then I put Criteria 1, i.e. 5, in cell E15. And Criteria 2, 24.4%, in cell E16. Then the formula to get the result you mentioned is

=INDEX($B$1:$H$1,MATCH(F16,INDIRECT("B"&F15+1&":"&"H"&F15+1),0))

You might need to adjust the ranges depending on where everything is in your workbook.

 

[lrobbo314]

I just re-read your post and think that the following formula is more what you are looking for. This one is an array formula so you need to hit Ctrl+Shift+Enter when entering the formula.

=INDEX($B$1:$H$1,MATCH(F16,ROUND(INDIRECT("B"&F15+1&":"&"H"&F15+1),2),0))

 

[Joelsephaz]

I just re-read your post and think that the following formula is more what you are looking for. This one is an array formula so you need to hit Ctrl+Shift+Enter when entering the formula.

=INDEX($B$1:$H$1,MATCH(F16,ROUND(INDIRECT("B"&F15+1&":"&"H"&F15+1),2),0))

This seems to work, but for example if I put in 8/24% it isn't returning "Orange" comes back as #n/a

 

[lrobbo314]

Maybe this...

=INDEX($B$1:$H$1,MATCH(E16,ROUND(INDIRECT("B"&E15+1&":"&"H"&E15+1),2),1))

Although, 8/24 is 0.3333 and I don't see a situation where .3333 would ever return orange since orange ranges from .024 to .028.