VLOOKUP - Multiple Criteria

[gregula82]

Hi Everyone,

I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. e.g. the lookup value would have 3 separate criteria and then you put the table array in and the column index.

Any ideas???

Thanks.

Greg.

 

[sahibzada]

You can't use VLOOKUP like that but you can use a different formula, e.g. to find the first row where the criteria is met in columns A, B and C and then return the value from D

=INDEX(D1:D100,MATCH(1,(A1:A100="x")*(B1:B100="y")*(C1:C100="z"),0))

confirmed with CTRL+SHIFT+ENTER

What if i want to get the last row where criteria is met?

 

[hope/despair]

Hi

The formula works great for me, i just wanted to know what is the function of '1' the first argument in the Match function?

=INDEX(D1:D100,MATCH(1,(A1:A100="x")*(B1:B100="y")*(C1:C100="z"),0))

 

[T. Valko]

Hi

The formula works great for me, i just wanted to know what is the function of '1' the first argument in the Match function?

=INDEX(D1:D100,MATCH(1,(A1:A100="x")*(B1:B100="y")*(C1:C100="z"),0))

Assuming there is only one instance where:

A1:A100="x" AND B1:B100="y" AND C1:C100="z"

When all those logical tests are multiplied together the result will be an array of 0s and a 1. Then we want to find the 1 in that array and return the corresponding value from column D.

Let's see how that works with this sample data:

Book1
	A	B	C	D
1	A	G	Z	Data1
2	X	Y	Z	Data2
3	G	Y	T	Data3
4	R	R	R	Data4
5	X	P	Y	Data5
Sheet1

This array formula**:

=INDEX(D1:D5,MATCH(1,(A1:A5="x")*(B1:B5="y")*(C1:C5="z"),0))

Result = Data2

Each of these expressions will return an array of TRUE or FALSE:

(A1:A5="x")
(B1:B5="y")
(C1:C5="z")

A1 = X = FALSE
A2 = X = TRUE
A3 = X = FALSE
A4 = X = FALSE
A5 = X = TRUE

B1 = Y = FALSE
B2 = Y = TRUE
B3 = Y = TRUE
B4 = Y = FALSE
B5 = Y = FALSE

C1 = Z = TRUE
C2 = Z = TRUE
C3 = Z = FALSE
C4 = Z = FALSE
C5 = Z = FALSE

Then these arrays are multiplied together:

FALSE * FALSE * TRUE = 0
TRUE * TRUE * TRUE = 1
FALSE * TRUE * FALSE = 0
FALSE * FALSE * FALSE = 0
TRUE * FALSE * FALSE = 0

We now have this array:

0
1
0
0
0

When we align that array with the range we want the result to come from:

0 Data1
1 Data2
0 Data3
0 Data4
0 Data5

So we look for the 1 and return the result from column D that corresponds to the 1.

=INDEX({Data1;Data2;Data3;Data4;Data5},MATCH(1,{0;1;0;0;0},0))

MATCH returns the *relative* position of the lookup value within an array. In this case MATCH = 2.

Return the value from position 2 of this array:

Position 1 = Data1
Position 2 = Data2
Position 3 = Data3
Position 4 = Data4
Position 5 = Data5

Position 2 = Data2

So:

=INDEX(D1:D5,MATCH(1,(A1:A5="x")*(B1:B5="y")*(C1:C5="z"),0))

= Data2

 

[zookeepertx]

T. Valko,

This is probably the single BEST explanation I've ever seen of a concept I've been really struggling with!! I'm very "visual" - words just don't do me much good - & your explanation makes immediate sense to me!!

Hopefully, I can use this to help me wrestle a formula I've been fighting with for 3 days now into submission.

Thanks!

Jenny

 

[T. Valko]

T. Valko,

This is probably the single BEST explanation I've ever seen of a concept I've been really struggling with!! I'm very "visual" - words just don't do me much good - & your explanation makes immediate sense to me!!

Hopefully, I can use this to help me wrestle a formula I've been fighting with for 3 days now into submission.

Thanks!

Jenny

Thanks for the feedback!

Things are a lot easier when you actually understand the logic and the mechanics of what's happening in a formula.

 

[Va1bhav]

There is one more sleek way to do this
(Quoting Example of Apples, Colour & Origin)

A B C D
1 ID Type Colour Origin
2 66566 Apple Green South Africa
3 66567 Apple Yellow France
4 66568 Apple Green France
5 66588 Apple Yellow South Africa
6
7 Type Colour Origin Answer
8 Apple Green South Africa 66566

=+DGET(A1:D5,"ID",A7:C8) in Cell {D8}

 

[MarcoSpring]

I am using this formula and how can I have the return a 0 instead of #N/A.

"=INDEX(D1:D100,MATCH(1,(A1:A100="x")*(B1:B100="y")*(C1:C100="z"),0))"

Thanks
marco

 

[Aladin Akyurek]

I am using this formula and how can I have the return a 0 instead of #N/A.

"=INDEX(D1:D100,MATCH(1,(A1:A100="x")*(B1:B100="y")*(C1:C100="z"),0))"

Thanks
marco

Excel 2007 or later...
Control+shift+enter, not just enter:

=IFERROR(INDEX(D1:D100,MATCH(1,IF(A1:A100="x",IF(B1:B100="y",
  IF(C1:C100="z",1))),0)),0)

All versions...
Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
  INDEX(D1:D100,MATCH(1,IF(A1:A100="x",IF(B1:B100="y",
   IF(C1:C100="z",1))),0))))

Note. The foregoing filters with IF throughout instead of pairwise multiplications.

 

[MarcoSpring]

Thank you very much, it works.

Marco

 

[Aladin Akyurek]

Thank you very much, it works.

Marco

You are welcome. Thanks for providing feedback.