Vlook up with multiple conditions

[SimonaB]

Hello,

I am in some trouble when trying to use vlook up with multiple conditions. I have read a few threads but I can't seem to make it work for my task.

Can you explain me on this example?
We have two products in two different storage locations each. I want to see sales matched with the stock here given that in my excel they are in different tables.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Warehouse[/TD]
[TD]Stock[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Sales[/TD]
[TD]Warehouse[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]123[/TD]
[TD]70[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]B[/TD]
[TD]150[/TD]
[TD][/TD]
[TD]1234[/TD]
[TD]25[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]A[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]1234[/TD]
[TD]150[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]B[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would kindly appreciate your help, it would be a life-saver!!

Thanks in advance!

Simona

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G	H
1	Product	Warehouse	Stock		Product	Sales	Warehouse
2	123	A	100		123	70	A	100
3	123	B	150		1234	25	A	50
4	1234	A	50		1234	150	B	200
5	1234	B	200
Sheet10

Cell Formulas
Range	Formula
H2	{=INDEX($C$2:$C$5,MATCH(E2&G2,$A$2:$A$5&$B$2:$B$5,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[SpillerBD]

Whether single or multiple criteria, the Index(Match) can be an important alternative to VLOOKUP.
In many cases I prefer INDEX(MATCH) because of the large arrays I regularly deal with or the fact that I want to pull from the left of the matched value.

(Now I should plug Mike Girvin's book.)

 

[SimonaB]

Hello,

Thanks for the reply. However, the formula does not work properly because it does not always return the correct sales value...

Any idea what I might be doing wrong?

This?

Excel 2010

	A	B	C	D	E	F	G	H
Product	Warehouse	Stock	Product	Sales	Warehouse
A	A
B	A
A	B
B

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]70[/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]150[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]25[/TD]

[TD="align: right"]50[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1234[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]150[/TD]

[TD="align: right"]200[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1234[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]{=INDEX($C$2:$C$5,MATCH(E2&G2,$A$2:$A$5&$B$2:$B$5,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[SimonaB]

Actually, I solved it with input from here: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/

By adding match value and the column number it works... maybe it helps you too, check it out.

Thank you kindly though for the support, I truly appreciate it!

 

[sheetspread]

Glad you got it to work but as you see the formula above returned all three matches, or did you want something different?