Backwards LookUp
 

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

Backwards LookUp
 

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.

--
Biff
Microsoft Excel MVP


"Eli" wrote in message
...
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

Backwards LookUp
 

That worked flawlessy, thank you.

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Eli" wrote in message
...
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

Backwards LookUp
 

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.


--
Biff
Microsoft Excel MVP


"Eli" wrote in message
...
That worked flawlessy, thank you.

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Eli" wrote in message
...
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