HOW TO MATCH DATA ONE WORKBOOK TO OTHER WORKBOOK & MATCHING DATA

[krunal123]

# 1st Condition : MATCH Data 1st "SHEET 1 WITH '"SHEET 2"

# 2nd Condition : "SHEET 2" MATCH DATA with other Workbook "SHEET 1" like this

BOTH CONDITION MATCH AFTER SHOW ME STATUS " YES / NO " in Worksheet 2 > SHEET 1

 

[Alex Blakenburg]

I am not 100% clear on what you are trying to achieve but as it was described I don't see how the order of checking Sheet 1 to Sheet 2 first really matters.
The way I am reading it, if any of the matches fails you want to return a NO in the Status column.

The file path and file name will be invalid on your system so the below will give you NO and try and find a file, you will need to put in the file path and workbook name you need.
There are simpler functions than XLookup for the 3rd check put I have worked on the assumption you need a function that works on a closed workbook.

20210330 Xlookup Closed workbook etc.xlsx
	A	B	C	D	E
1
2	No	Products	Qty	Amt	Status
3	1	Coconut Oil			NO
4		500 ML x 48 BTL
5	2	Mustard Oil			NO
6		750 ML x 96 BTL
7	3	RICE			NO
8		50 KG x 6 BAG
Sheet1

Cell Formulas
Range	Formula
E3:E8	E3	=IF(ISNUMBER(A3), IF(OR( ISNA(MATCH(B3,'C:\Test\[Worksheet 1.xlsx]Sheet1'!$A$2:$A$6,0)), ISNA(MATCH(B4,'C:\Test\[Worksheet 1.xlsx]Sheet1'!$C$2:$C$7,0)), ISNA(XLOOKUP(1,('C:\Test\[Worksheet 1.xlsx]Sheet2'!$A$3:$A$5=B3)*('C:\Test\[Worksheet 1.xlsx]Sheet2'!$B$3:$B$5=B4),'C:\Test\[Worksheet 1.xlsx]Sheet2'!$A$3:$A$5,#N/A,0)) ), "NO","YES"),"")