Combination of Vlookup & Hlookup

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

Guest

For example:
Apple Orange Pear
A 1 4 7
B 2 5 8
C 3 6 9

How do I combine vlookup and hlookup to pick up the value in Row B and
Column Orange which will be 5?

Appreciate your help. Thank you.
 
Hi!

Try one of these:

=VLOOKUP("B",A1:D4,MATCH("orange",A1:D1,0),0)

=INDEX(B2:D4,MATCH("B",A2:A4,0),MATCH("orange",B1:D1,0))

Better to use cells to hold the lookup criteria.

Biff
 
Dave Peterson and Biff have both provided good answers.

You might find my own short tutorial about using Match() and Index() for
this type of lookup helpful. You can download a .pdf file and an associated
..xls file that tries to explain it all in an understandable way here:
http://www.jlathamsite.com/teach/ExcelMatchAndIndexLesson.pdf
http://www.jlathamsite.com/teach/ExcelMatchAndIndexLesson.xls

Hope this helps some. There's also recommended reading mentioned in the
..pdf document to help you on your journeys through Excel-land.
 
Highlight your table; click on Insert|Name|Create and select Top row and
Left column

Then =Orange B will return 5, =Pear C will return 9, etc.

Alan Beban
 
Oops! Except for some arcane Excel reason you can't use C or R as row
names. You could substitute _C and _R.

Alan Beban
 
Back
Top