VLookup and HLookup at the same time

S

Steven

How do you do a vlookup and hlookup at the same time:

D E F
A 1 4 7
B 2 5 8
C 3 6 9

Where for example A E = 4

Thank you for your help.

Steven
 
J

John Michl

You could use a combination of Match and Index. Assuming your table is
in the range A1:D4 (where A1 is blank and D4 = 9), the following will
work:

=INDEX(B2:D4,MATCH("A",A2:A4),MATCH("E",B1:D1))

- John
 
D

Domenic

Try...

=INDEX(B2:D4,MATCH("A",A2:A4,0),MATCH("E",B1:D1,0))

or

=INDEX(B2:D4,MATCH(F1,A2:A4,0),MATCH(G1,B1:D1,0))

....where F1 contains your first criterion, such as A, and G1 contains
your second criterion, such as E.

Hope this helps!
 
A

Alan Beban

Steven said:
How do you do a vlookup and hlookup at the same time:

D E F
A 1 4 7
B 2 5 8
C 3 6 9

Where for example A E = 4

Thank you for your help.

Steven
Click to expand...
Highlight your data range, including the headings; click on
Insert|Name|Create; check Top row and Left Column, OK. Then
=A D will return 4, =B D will return 2, etc.

Alan Beban
 
Top