VLOOKUP but with two lookup values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hall - 12 Apr 2007 04:44 GMT

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!

Reply to this Message

Max - 12 Apr 2007 05:18 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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?
[quoted text clipped - 16 lines]
>
> Thanks y'all!

Reply to this Message

T. Valko - 12 Apr 2007 05:23 GMT

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

> 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?
[quoted text clipped - 16 lines]
>
> Thanks y'all!

Reply to this Message

Rate this thread: