Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Table Lookup formula where 2 known values are inside array
I am using Excel.
Please help, I am stuck on a non-typical table lookup I regularly use =lookup to get values where the relationship is 1:1 e.g. in a cell, I put a value, I use =lookup for that value's meaning I need to do this, but the lookup is a table, not a 1:1 column match Row 1 is a header, one of my search values Column 1 is a value I want to find the table (array) contains the other known value I need do do a enter values in 2 cells, and have a lookup return a value based on the value it looks up. eg: -- | 1 | 2 | 3 | 4 w | a | d | h | i x | b | e | i | j y | c | f | j | k z | d | g | k | l in my lookup, one cell has the value 2, This should specify to use the column with the value 2 in the first row The other cell has the value f. The formula I am having a hard time figuring out needs to return the value in column 1, which in this case is the letter y. I have tried vlookup, hlookup, match and so on, but I am just plain stuck. Please help. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Table Lookup formula where 2 known values are inside array
Try this:
With your table in the range A1:E5 A10 = column lookup_value = 2 B10 = table lookup_value = F =INDEX(A2:A5,MATCH(B10,INDEX(B2:E5,,MATCH(A10,B1:E 1,0)),0)) Biff "excel-lookuper" wrote in message ... I am using Excel. Please help, I am stuck on a non-typical table lookup I regularly use =lookup to get values where the relationship is 1:1 e.g. in a cell, I put a value, I use =lookup for that value's meaning I need to do this, but the lookup is a table, not a 1:1 column match Row 1 is a header, one of my search values Column 1 is a value I want to find the table (array) contains the other known value I need do do a enter values in 2 cells, and have a lookup return a value based on the value it looks up. eg: -- | 1 | 2 | 3 | 4 w | a | d | h | i x | b | e | i | j y | c | f | j | k z | d | g | k | l in my lookup, one cell has the value 2, This should specify to use the column with the value 2 in the first row The other cell has the value f. The formula I am having a hard time figuring out needs to return the value in column 1, which in this case is the letter y. I have tried vlookup, hlookup, match and so on, but I am just plain stuck. Please help. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Table Lookup formula where 2 known values are inside array
Thank you, your method and formula worked perfectly!
"T. Valko" wrote: Try this: With your table in the range A1:E5 A10 = column lookup_value = 2 B10 = table lookup_value = F =INDEX(A2:A5,MATCH(B10,INDEX(B2:E5,,MATCH(A10,B1:E 1,0)),0)) Biff "excel-lookuper" wrote in message ... I am using Excel. Please help, I am stuck on a non-typical table lookup I regularly use =lookup to get values where the relationship is 1:1 e.g. in a cell, I put a value, I use =lookup for that value's meaning I need to do this, but the lookup is a table, not a 1:1 column match Row 1 is a header, one of my search values Column 1 is a value I want to find the table (array) contains the other known value I need do do a enter values in 2 cells, and have a lookup return a value based on the value it looks up. eg: -- | 1 | 2 | 3 | 4 w | a | d | h | i x | b | e | i | j y | c | f | j | k z | d | g | k | l in my lookup, one cell has the value 2, This should specify to use the column with the value 2 in the first row The other cell has the value f. The formula I am having a hard time figuring out needs to return the value in column 1, which in this case is the letter y. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Table Lookup formula where 2 known values are inside array
You're welcome. Thanks for the feedback!
Biff "excel-lookuper" wrote in message ... Thank you, your method and formula worked perfectly! "T. Valko" wrote: Try this: With your table in the range A1:E5 A10 = column lookup_value = 2 B10 = table lookup_value = F =INDEX(A2:A5,MATCH(B10,INDEX(B2:E5,,MATCH(A10,B1:E 1,0)),0)) Biff "excel-lookuper" wrote in message ... I am using Excel. Please help, I am stuck on a non-typical table lookup I regularly use =lookup to get values where the relationship is 1:1 e.g. in a cell, I put a value, I use =lookup for that value's meaning I need to do this, but the lookup is a table, not a 1:1 column match Row 1 is a header, one of my search values Column 1 is a value I want to find the table (array) contains the other known value I need do do a enter values in 2 cells, and have a lookup return a value based on the value it looks up. eg: -- | 1 | 2 | 3 | 4 w | a | d | h | i x | b | e | i | j y | c | f | j | k z | d | g | k | l in my lookup, one cell has the value 2, This should specify to use the column with the value 2 in the first row The other cell has the value f. The formula I am having a hard time figuring out needs to return the value in column 1, which in this case is the letter y. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix Math using LOOKUP inside Array {} Function | Excel Worksheet Functions | |||
vLookup with multiple lookup value in table array | Excel Worksheet Functions | |||
lookup a list of values for an array formula | Excel Worksheet Functions | |||
How do I delete a lookup table array name? | Excel Worksheet Functions | |||
find maximum of two values in an array with same lookup value | Excel Discussion (Misc queries) |