Backwards LookUp

E

Eli

This will be easier to explain with an example:

A B C D
1 1 2 3
2 1 3.7 5.4 5.4
3 2 6.5 10.8 10.8
4 3 13.1 16.2 23.4

The object of the formula that I am trying to write is to return the
corresponding value in A2:A4 that matches the combination of B1:D1 and B2:D4.
So for example if the number in B1:D1 that I know is 2 and the number in
B2:D4 that I know is 16.2 then I want 3 (the value in A4) returned as the
answer.

To add one more twist to this. If the number I know in B1:D1 is 3 but the
number I know in the B2:D4 is 7, I want the formula to look for the next
larger number that is in the table to match with, so in this example the
correct answer would be 2 (A3).

Any ideas? Thank you, Eli
 
T

T. Valko

Try this array formula**.

Assumes the lookup value will not be greater than the max value in its
respective column.

A10 = column number
B10 = lookup value

=INDEX(A2:A4,MATCH(TRUE,INDEX(B2:D4,,MATCH(A10,B1:D1,0))>=B10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
E

Eli

That worked flawlessy, thank you.

T. Valko said:
Try this array formula**.

Assumes the lookup value will not be greater than the max value in its
respective column.

A10 = column number
B10 = lookup value

=INDEX(A2:A4,MATCH(TRUE,INDEX(B2:D4,,MATCH(A10,B1:D1,0))>=B10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
Click to expand...
 
T

T. Valko

You're welcome. Thanks for the feedback!

As a follow-up...

I was going on the assumption that your column headers aren't really the
numbers 1, 2, 3.

If they really are the sequential numbers 1, 2, 3 then you can shorten the
formula to:

=INDEX(A2:A4,MATCH(TRUE,INDEX(B2:D4,,A10)>=B10,0))

Still array entered.
 
Top