Vlookup with multiple criteria

[rhmkrmi]

Hi there,

Is there a way I can use VLOOKUP based on two criteria in two separate columns other than creating a columns with concatenated values as lookup?
I need to check a value in column A and then another one in column B to find the right match in column C.

Thank you.

 

[Yongle]

One way

Book1
	A	B	C	D	E	F	G
1	Name1	Name2	Value		Name1	Name2	RESULT
2	John	Smith	1		Mickey	Mouse	5
3	John	Peters	2
4	John	James	3
5	Jessie	James	4
6	Mickey	Mouse	5
7	Minnie	Mouse	6
8	John	Mouse	7
Sheet1

Cell Formulas
Range	Formula
G2	G2	=INDEX(C:C,MATCH(E2&F2,A:A&B:B,0))

 

[Peter_SSs]

One way

If using INDEX/MATCH by concatenating values, I would suggest a couple of changes ..

• I would not use whole columns as joining over a million values is relatively slow so if there are a lot of these formulas in a worksheet they could start to impact the performance speed of the sheet.
• Add a delimiter just in case a circumstance like below might arise and a wrong result occurs as happens in G2.

Depending on your Excel version, you may need to confirm these formulas with Ctrl+Shift+Enter, not just Enter.

20 07 06.xlsm
	A	B	C	D	E	F	G	H
1	Name1	Name2	Value		Name1	Name2	RESULT	RESULT
2	John	Smith	1		Mickey	Mouse	2	5
3	Mick	Eymouse	2
4	John	James	3
5	Jessie	James	4
6	Mickey	Mouse	5
7	Minnie	Mouse	6
8	John	Mouse	7
9
10
Lookup

Cell Formulas
Range	Formula
G2	G2	=INDEX(C:C,MATCH(E2&F2,A:A&B:B,0))
H2	H2	=INDEX(C2:C10,MATCH(E2&"|"&F2,A2:A10&"|"&B2:B10,0))

@rhmkrmi
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
For example, the suggestion in column H below requires Excel 365.

So, a couple of other alternatives if you don't want to concatenate the values are:

20 07 06.xlsm
	A	B	C	D	E	F	G	H
1	Name1	Name2	Value		Name1	Name2	RESULT	RESULT
2	John	Smith	1		Mickey	Mouse	5	5
3	Mick	Eymouse	2
4	John	James	3
5	Jessie	James	4
6	Mickey	Mouse	5
7	Minnie	Mouse	6
8	John	Mouse	7
9
10
Lookup (2)

Cell Formulas
Range	Formula
G2	G2	=INDEX(C$2:C$10,AGGREGATE(15,6,(ROW(C$2:C$10)-ROW(C$2)+1)/((A$2:A$10=E2)*(B$2:B$10=F2)),1))
H2	H2	=INDEX(FILTER(C$2:C$10,(A$2:A$10=E2)*(B$2:B$10=F2)),1)

 

[rhmkrmi]

Thank you both, works perfectly.

 

[Peter_SSs]

Thank you both, works perfectly.

Glad you got something that suited you.

... but what about updating your profile anyway?