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.

Product	Warehouse	Stock		Product	Sales	Warehouse
123	A	100		123	70	A
123	B	150		1234	25	A
1234	A	50		1234	150	B
1234	B	200

<tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet10​

Array Formulas Cell Formula H2 {=INDEX($C$2:$C$5,MATCH(E2&G2,$A$2:$A$5&$B$2:$B$5,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

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