Vlookup when multiple same value in both columns

[mayaa_mmm]

I want to vlookup with supplier and product with other file "Comparing file" which has supplier and product. if value matches and status is completed , Mark as Yes in output

Supplier	Product	Status	Output
AB	XY-1	Completed	Yes
AB	XY-1	Completed	Yes
AB	XY-2	Completed	Yes
BC	ZA-1	Pending	No
AC	ZC	Completed	Yes

Vlookup with this file"Comparing file"

Supplier
AB	XY-1
AB	XY-1
AB	XY-2
BC	ZA-1
AC	ZC
AE	QA
AD	ZD

 

[Fluff]

How about

+Fluff.xlsm
	A	B
1	Supplier
2	AB	XY-1
3	AB	XY-1
4	AB	XY-2
5	BC	ZA-1
6	AC	ZC
7	AE	QA
8	AD	ZD
Sheet2

+Fluff.xlsm
	A	B	C	D
1	Supplier	Product	Status	Output
2	AB	XY-1	Completed	Yes
3	AB	XY-1	Completed	Yes
4	AB	XY-2	Completed	Yes
5	BC	ZA-1	Pending	No
6	AC	ZC	Completed	Yes
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=IF(AND(C2="Completed",ISNUMBER(MATCH(A2&"|"&B2,INDEX(Sheet2!$A$2:$A$8&"|"&Sheet2!$B$2:$B$8,0),0))),"Yes","No")

 

[mayaa_mmm]

How about

+Fluff.xlsm
	A	B
1	Supplier
2	AB	XY-1
3	AB	XY-1
4	AB	XY-2
5	BC	ZA-1
6	AC	ZC
7	AE	QA
8	AD	ZD
Sheet2

+Fluff.xlsm
	A	B	C	D
1	Supplier	Product	Status	Output
2	AB	XY-1	Completed	Yes
3	AB	XY-1	Completed	Yes
4	AB	XY-2	Completed	Yes
5	BC	ZA-1	Pending	No
6	AC	ZC	Completed	Yes
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=IF(AND(C2="Completed",ISNUMBER(MATCH(A2&"|"&B2,INDEX(Sheet2!$A$2:$A$8&"|"&Sheet2!$B$2:$B$8,0),0))),"Yes","No")

Thank you for inputs.
When i run the formula .I am getting No in column the columsn
I need in vba code . Will it be possible?

 

[Fluff]

Why do you need VBA?
Also it would be a good idea to state that in the beginning, to avoid members wasting their time, offering a solution you have no interest in.

 

[mayaa_mmm]

Why do you need VBA?
Also it would be a good idea to state that in the beginning, to avoid members wasting their time, offering a solution you have no interest in.

Formula is fine. No problem for me. But i m receiving No in output

 

[Fluff]

Is your data laid out as shown in my two examples?
Also have you change the sheet name to suit?

 

[mayaa_mmm]

H

Is your data laid out as shown in my two examples?
Also have you change the sheet name to suit?

thank you for your update. Yes , Updated the sheet name. Now working fine. I need one more requirement.
I need formula without Column C inputs "completed"

Only comparing Supplier and Product, output is product value in the D2
is it possible to get output

Supplier	Product	Status	Output
AB	XY-1	Completed	XY-1
AB	XY-1	Completed	XY-1
AB	XY-2	Completed	XY-2
BC	ZA-1	Pending	ZA-1
AC	ZC	Completed	ZC

Vlookup with this file"Comparing file"

Supplier
AB	XY-1
AB	XY-1
AB	XY-2
BC	ZA-1
AC	ZC
AE	QA
AD	ZD

 

[Fluff]

Ok, how about
=IFERROR(INDEX(Sheet2!$B$2:$B$8,MATCH(A2&"|"&B2,INDEX(Sheet2!$A$2:$A$8&"|"&Sheet2!$B$2:$B$8,0),0)),"")