Match function

[tflee]

Im trying to use match function to find a place in a table but the second
variable could be a number between two number for example
this is the formula that i am using

=INDEX(B3:E4,MATCH(G2,A3:A6,0),MATCH(I2,B2:E2,1))
here is the table
1000-5000 5001-8000 8001-1200012001-32000
2 1.72 1.15 0.92 0.86
3 1.15 0.92 0.86 0.72

the second variable in the formula could be a number between 1000-5000 for
example 3000 and all i get in return is #N/A
please help

 

[Shane Devenshire]

Hi,

Just change the first row to read 5000, 8000, 12000, 32000

Then =INDEX(B3:E4,MATCH(G2,A3:A6,0),MATCH(I2,B2:E2,1)) will work.

Or you could use

=VLOOKUP(G2,A3:E6,MATCH(I2,A2:E2,1))

or a variation depending on your data.