Look up range on two variable table

[dhyun3]

Hi all,

In advance, thanks for helping.

I'm trying to put formula to find a value based on two variables. I was able to put formula to find it on two exact match, but my formula doesn't work when one variable is between other variables.

For example, I have $250 million incremental for revenues from $250 million to $2 billion. If a firm has a revenue of $300 million, it gives me an error. I just can't seem to figure out how to add a range formula.

Please see attached.

Thanks!

[benishiryo]

hi dhyun3. as long as row 2 is in ascending order, you can use approximate match. that is putting in 1 or not putting anything in the last argument of MATCH. it will try to find an exact match, but when it fails, it will use the last lowest number. The ones in row 2 should be based on the minimum value it requires for the category
=VLOOKUP(D14,$B$3:$J$9,MATCH(C14,$B$2:$J$2),FALSE)

or:
=VLOOKUP(D14,$B$3:$J$9,MATCH(C14,$B$2:$J$2),0)

[newdoverman]

In order to get the correct answer, I had to use this minor difference in the formula given by Benishiryo because I always got the result from 1 column to the left of where I expected.

Please Login or Register  to view this content.

This also works:

Please Login or Register  to view this content.