Table Lookup formula where 2 known values are inside array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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:E1,0)),0))

Biff
 
Back
Top