index match with the lookup column having ranges

[laxcoug]

Thanks in advance for any suggestions. I am trying to do a 2 way lookup using index match. The first part of the match will be to the column A2:A10 where the values are arranged in ranges. A2 would be 1-5, A3 would be 6-10, A4 would be 11-15 ect.

So I can't seem to make it work but I think it should look something like =index(B2:E10,Match(J20,A2:A10,[WHAT SHOULD I PUT HERE?]),MATCH(J21,B1:H1,0))

 

[tigeravatar]

Can you give a sample of your data and expected results?

 

[circledchicken]

Hi,

One option is instead of (or in addition to) having ranges, put the lower bound of each row in the cell instead, like this:

Excel 2013

	A	B
1	Range	Instead of
2	1	1-5
3	6	6-10
4	11	11-15

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Then use match_type 1 in your formula:
=MATCH(J20, A2:A10, 1)

Here's a quick reference on the Match function:
MS Excel: MATCH Function (WS)

 

[laxcoug]

Yes. Do I do that by downloading a file?

 

[laxcoug]

I will try this. Thank you.