Combination of Vlookup & Hlookup

[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.

 

[Dave Peterson]

Maybe =index(match())

Debra Dalgleish explains it here:
http://www.contextures.com/xlFunctions03.html

 

[Biff]

Hi!

Try one of these:

=VLOOKUP("B",A14,MATCH("orange",A11,0),0)

=INDEX(B24,MATCH("B",A2:A4,0),MATCH("orange",B11,0))

Better to use cells to hold the lookup criteria.

Biff

 

[Guest]

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.

 

[Alan Beban]

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

 

[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