IF AND STATEMENT with XLOOKUP

[volsfan210000]

I have two excel sheets with columns of data.

Sheet 1 I have column A (Data Set 1) & Column B (Data Set 2) which represent the fields I want to look for in sheet 1

Sheet 2 is the raw data and i want the if and statement to look for in Column A & Column C

I want the formula to tell me if sheet 1 has the data that sheet 2 has.

 

[alansidman]

Cannot manipulate data in a picture. Please resubmit using XL2BB.

 

[awoohaw]

I don't see a column C in you rexample. So I've made it up.
The #N/A means the combination was not found:

Also, this only get the first match found. So if a Projt number and CODE appear twice in Sheet2 you will only get the first record.

Book1
	A	B	C	D	E	F	G
1	Sheet1				Sheet2
2
3	ColA	ColB	LOOKUP		ColA	ColB	ColC
4
5	Projt1	ABC	A		Projt1	ABC	A
6	Projt2	123	#N/A		Projt2	ABC	B
7	Projt3	456	#N/A		Projt3	ABV	C
8	Projt4	789	#N/A		Projt4	123	D
9	Projt5	ABC	#N/A		Projt5	456	E
10	Projt6	123	#N/A		Projt6	789	F
11	Projt7	456	#N/A		Projt7	ABC	G
12	Projt8	789	#N/A		Projt8	ABC	H
13	Projt9	ABC	I		Projt9	ABC	I
14	Projt10	123	#N/A		Projt10	ABC	J
15	Projt11	456	#N/A		Projt11	ABV	K
16	Projt12	789	#N/A		Projt12	123	L
17	Projt13	ABC	#N/A		Projt13	456	M
18	Projt14	123	#N/A		Projt14	789	N
19	Projt15	456	O		Projt15	456	O
20	Projt16	789	P		Projt16	789	P
Sheet1

Cell Formulas
Range	Formula
C5:C20	C5	=INDEX(G5:G20,MATCH(A5&B5,E5:E20&F5:F20,0))

 

[alansidman]

If you have more than one match per item in the first table, then you can gather all matches using Power Query

let
    T1= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RemoveNull = Table.SelectRows(T1, each [ColA] <> null),
    T2= Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    RemoveNulls = Table.SelectRows(T2, each ([ColA] <> null)),
    Join= Table.NestedJoin(RemoveNull,{"ColA","ColB"},RemoveNulls,{"ColA","ColB"},"T3",JoinKind.LeftOuter),
    ExpandedT3 = Table.ExpandTableColumn(Join, "T3", {"ColC"}, {"ColC"})
in
    ExpandedT3