Index match help

[proctk]

HI,

I'm trying to figure out how to write a formula that will look down through Range(A5:A16) and find the value in cell A2. The next step is to look accross C3:H3 and find the value in cell A3.

Here is the tricky part. The value in A3 needs to be found between the number value in the Range (C3:H3). The fomula is meant to work the sale way as a times table.


C35,000
D3 25,000
E3 100,000
F3 499,999
G3 100,000
H3 5,000,000

Thank you for any help

 

[njimack]

=index(c3:h16,match(a2,a5:a16,0),match(a3,c3:h3,0))

 

[proctk]

here is an if statement that looks for the correct value

=IF(B20<=C3,1,IF(B20<=D3,2,IF(B20<=E3,3,IF(B20<=F3,4,IF(B20<=G3,5,IF(B20<=H3,6,7))))))

This needs to make up the match for the column search

 

[proctk]

Here is the match formula that works.

=INDEX(C5:H16,MATCH(A2,A5:A16,0),MATCH(B2,C3:H3,0))

I need to work the if statement I posted early to be used with the match statement to find the column

Thank you!

 

[barry houdini]

Try this

=INDEX(C5:H16,MATCH(A2,A5:A16,0),COUNTIF(C3:H3,"<"&B2)+1)