VLOOKUP but with two lookup values

[Hall]

VLOOKUP is great for looking up a value with a single index column. But
what if I need to lookup against two or more values?

Simplified example:

apples 5 A
apples 6 B
apples 4 C
pears 6 D
pears 7 E
grapes 4 F
oranges 2 G
oranges 3 H

Get value in third column where first column is "pears" and second column is
6. Result is D.

How would this be done??

Thanks y'all!

 

[Max]

Assuming source data in cols A to C, from row1 to 100

Criteria inputs:
In D1: pears, in E1: 6

Then in F1, array-entered with CSE*:
=INDEX($C$1:$C$100,MATCH(1,($A$1:$A$100=D1)*($B$1:$B$100=E1),0))
Copy F1 down to return correspondingly for other criteria sets in D2:E2,
D3:E3, etc

*press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER

 

[T. Valko]

Try this:

=INDEX(C2:C9,MATCH(1,INDEX((A2:A9="pears")*(B2:B9=6),,1),0))

Or, use cells to hold the criteria:

E2 = pears
F2 = 6

=INDEX(C2:C9,MATCH(1,INDEX((A2:A9=E2)*(B2:B9=F2),,1),0))

Biff