Vlookup with three criterias

[alfredo197135]

Hi guys,

Can you tell me how to use Vlookup with three criterias to find?

 

[T. Valko]

Hi guys,

Can you tell me how to use Vlookup with three criterias to find?

Like this...

Book1
	A	B	C	D
2	F	Y	Z	Result1
3	A	S	D	Result2
4	X	Y	Z	Result3
5	R	T	Y	Result4
6	_	_	_	_
7	X	Y	Z	Result3
Sheet1

We want to lookup X, Y, Z.

This array formula** entered in D7:

=INDEX(D2:D5,MATCH(1,IF(A2:A5=A7,IF(B2:B5=B7,IF(C2:C5=C7,1))),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[alfredo197135]

Thanks! that works.

 

[T. Valko]

Thanks! that works.

You're welcome. Thanks for the feedback!

 

[itisalikhan]

Hi there, i am not an expert. Just here to learn. I think you can also try the following array formula by pressing Ctrl+Shift+Enter

=INDEX($D$1:$D$4,MATCH($A$6&$B$6&$C$6,$A$1:$A$4&$B$1:$B$4&$C$1:$C$4,0))

 

[itisalikhan]

Hi there, i am not an expert. Just here to learn. I think you can also try the following array formula by pressing Ctrl+Shift+Enter

=INDEX($D$1:$D$4,MATCH($A$6&$B$6&$C$6,$A$1:$A$4&$B$1:$B$4&$C$1:$C$4,0))

Sorry for worng cell references. Here it is again
=INDEX($D$2:$D$5,MATCH($A$7&$B$7&$C$7,$A$2:$A$5&$B$2:$B$5&$C$2:$C$5,0))

 

[T. Valko]

Hi there, i am not an expert. Just here to learn. I think you can also try the following array formula by pressing Ctrl+Shift+Enter

=INDEX($D$1:$D$4,MATCH($A$6&$B$6&$C$6,$A$1:$A$4&$B$1:$B$4&$C$1:$C$4,0))

Yes, that will also work but concatenating the ranges is not as efficient as using the nested IF fversion especially on large ranges.

 

[itisalikhan]

Yes, that will also work but concatenating the ranges is not as efficient as using the nested IF fversion especially on large ranges.

Thanks for increasing my knowledge.

 

[T. Valko]

Thanks for increasing my knowledge.

You're welcome!

 

[excel_1317]

I want to use the above formula with 2 criterias, I modified the formula accordingly but it is not working. Please help me correct it...

=INDEX(Sheet2!G2:G110511,MATCH(1,IF(Sheet2!E2:E110511=A2,if(Sheet2!F2:F110511=B2))),0))