Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with backwards lookup | Excel Discussion (Misc queries) | |||
Backwards? lookup function | Excel Worksheet Functions | |||
Excel is Backwards! | Setting up and Configuration of Excel | |||
Vlookup backwards | Excel Discussion (Misc queries) | |||
Backwards lookup | Excel Worksheet Functions |