Vlook-up two criteria in two sheets

[FGaxha]

Hi master,
I need help in vlookup formula.

Sheet1:A B C
Sheet2: A B C D

In sheet1 column C: =vlook-up(A&B,sheet2! A&B,3,0)

Thank you.

 

[Kaper]

One of possibilities will be:

=INDEX(Sheet2!$D$1:$D$10,SUM((A1=Sheet2!$A$1:$A$10)*(B1=Sheet2!$B$1:$B$10)*ROW($1:$10)))

replace all $10 with your last row in Sheet2 like $1000 or something like that. In older versions of excel this formula has to be array commmitted (with Ctrl+Shift+Enter, not just with Enter)

Another one, as you are using Excel 365 will be:

=XLOOKUP(A1&B1;Sheet2!A:A&Sheet2!B:B;Sheet2!D:D;;0)

 

[RobP]

Hi, I can't be 100% sure what you are trying to do, but this was an alternative approach that I took, assuming you are on O365 as you say.

Book1
	A	B	C
1
2	Steve	Thomas	data1
3	Rich	Hall	data2
4	Ian	Danter	data3
5	Fred	Hall
6	Jim	Jones
7
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(Sheet2!C$1:C$5,(Sheet2!A$1:A$5 = A2)*(Sheet2!B$1:B$5 = B2),"")

Book1
	A	B	C
1	Steve	Thomas	data1
2	Rich	Hall	data2
3	Ian	Danter	data3
4	Fred	Hall	data4
5	Jim	Jones	data5
Sheet2

 

[RobP]

Great, glad it worked, thanks for the feedback.

Rob