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.

 

[seilent]

ah, sorry for that

here's the sheet1

Row\Col	A​	B​	C​
1​	Shop A​
2​	Date	Item	Total​
3​	01 Oktober 2015​	Banana	12​
4​		Apple	10​
5​		Watermelon	8​
6​		Grape	1​
7​	02 Oktober 2015​	Apple	2​
8​		Pineapple	5​
9​		Mango	9​
10​		Watermelon	10​

<tbody>
</tbody>

Sheet2

Row\Col	A​	B​	C​
1​	Shop B
2​	Date	Item	Total​
3​	01 Oktober 2015​	Grape	12​
4​		Watermelon	10​
5​		Mango	8​
6​	02 Oktober 2015​	Banana	1​
7​		Watermelon	2​
8​		Grape	5​
9​		Apple	9​
10​		Mango	10​

<tbody>
</tbody>

Result Wanted (Another Workbook)

Sheet 1 Result (October 1st)

Row\Col	A​	B ​
3 ​	Item	Total​
4​	Banana	12​
5​	Apple	10​
6​	Watermelon	18​
7​	Grape	13​
8 ​	Mango	8​

<tbody>
</tbody>

Sheet 2 Result (October 2nd)

Row\Col	A ​	B ​
3​	Item	Total​
4​	Apple	11​
5​	Pineapple	5​
6​	Mango	19​
7​	Watermelon	12​
8​	Banana	1​
9​	Grape	5​

<tbody>
</tbody>

 

[neubie123]

I have a problem similar to the what the initial poster experienced. The formula provided appears like it would do the trick, but I cannot get it to work.

I get the #value error. Can someone tell me what the 1 is supposed to be?

=INDEX(Sheet1!A1:A1000,MATCH(1,(Sheet1!B1:B1000=$A2)*(Sheet1!C1:C1000=$B2)*(Sheet1!D1:D1000=$C2),0))

 

[Aladin Akyurek]

I have a problem similar to the what the initial poster experienced. The formula provided appears like it would do the trick, but I cannot get it to work.

I get the #value error. Can someone tell me what the 1 is supposed to be?

=INDEX(Sheet1!A1:A1000,MATCH(1,(Sheet1!B1:B1000=$A2)*(Sheet1!C1:C1000=$B2)*(Sheet1!D1:D1000=$C2),0))

You need to confirm the formula with control+shift+enter, not just enter. That means: Press down the control and the shift keys at the same time while you hit the enter key. By the way, the re-write can be a tad faster:

=INDEX(Sheet1!A1:A1000,MATCH(C2,IF(Sheet1!B1:B1000=$A2,IF(Sheet1!C1:C1000=$B2,Sheet1!D1:D1000)),0))

 

[neubie123]

You need to confirm the formula with control+shift+enter, not just enter. That means: Press down the control and the shift keys at the same time while you hit the enter key. By the way, the re-write can be a tad faster:

=INDEX(Sheet1!A1:A1000,MATCH(C2,IF(Sheet1!B1:B1000=$A2,IF(Sheet1!C1:C1000=$B2,Sheet1!D1:D1000)),0))

Thanks Aladin! But what is C2. Is it a lookup value?

 

[Aladin Akyurek]

Thanks Aladin! But what is C2. Is it a lookup value?

Yes, one of the three. Try to lock the relevant ranges if you are going to copy this formula...

=INDEX(Sheet1!$A$1:$A$1000,MATCH($C2,IF(Sheet1!$B$1:$B$1000=$A2,IF(Sheet1!$C$1:$C$1000=$B2,Sheet1!$D$1:$D$1000)),0))