VLookup and HLookup at the same time

[Guest]

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

 

[John Michl]

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

=INDEX(B24,MATCH("A",A2:A4),MATCH("E",B11))

- John

 

[Domenic]

Try...

=INDEX(B24,MATCH("A",A2:A4,0),MATCH("E",B11,0))

or

=INDEX(B24,MATCH(F1,A2:A4,0),MATCH(G1,B11,0))

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

Hope this helps!

 

[Alan Beban]

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

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