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

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Shop A​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Date
[/TD]
[TD]Item[/TD]
[TD]

Total​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

01 Oktober 2015​

[/TD]
[TD]Banana[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD]Watermelon[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD]Grape[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

02 Oktober 2015​

[/TD]
[TD]Apple[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD]Pineapple[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD]Mango[/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD]Watermelon[/TD]
[TD]

10​

[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Shop B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Date
[/TD]
[TD]Item[/TD]
[TD]

Total​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

01 Oktober 2015​

[/TD]
[TD]Grape[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD]Watermelon[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD]Mango[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

02 Oktober 2015​

[/TD]
[TD]Banana[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD]Watermelon[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD]Grape[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD]Mango[/TD]
[TD]

10​

[/TD]
[/TR]
</tbody>[/TABLE]

Result Wanted (Another Workbook)

Sheet 1 Result (October 1st)
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B
​

[/TD]
[/TR]
[TR]
[TD]

3
​

[/TD]
[TD]Item
[/TD]
[TD]

Total​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Banana[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Apple[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Watermelon[/TD]
[TD]

18​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Grape[/TD]
[TD]

13​

[/TD]
[/TR]
[TR]
[TD]

8
​

[/TD]
[TD]Mango[/TD]
[TD]

8​

[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 Result (October 2nd)
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A
​

[/TD]
[TD]

B
​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Item
[/TD]
[TD]

Total​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Apple[/TD]
[TD]

11​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Pineapple[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Mango[/TD]
[TD]

19​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Watermelon[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Banana[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Grape[/TD]
[TD]

5​

[/TD]
[/TR]
</tbody>[/TABLE]

 

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