Find, lookup, match - based on 2 separate criteria.

[ExcelBrent]

Hello world. I have 2 sheets, the first sheet contains a column of phone numbers (Sheet1!H:H) and a column of IDs (Sheet1!L:L), and the second sheet contains the same - phone numbers (Sheet2!E:E) and IDs (Sheet2!B:B).
Looking at it from a single-row perspective, I need to first find out if the phone number from Sheet1 H2 exists in Sheet2!E:E. Then, if it is found, compare the ID value from Sheet1 L2 to the Sheet2!B:B column but the same row on Sheet2 where the phone number match was found. If the IDs match, then return "MATCH", otherwise return the ID value from Sheet2.

Hopefully, that makes sense.
Any help would be greatly appreciated. Thank you!

 

[awoohaw]

try this, you'll need adjust the formulas to fit your workbook:

Book2
	A	B	C	D	E
1	Sheet2 Data
2		ID Nbr			PhoneNbr
3		A2333895			(994) 729-2689
4		A6460856			(994) 729-2681
5		A8193540			(990) 137-2115
6		A6460856			(972) 877-5079
7		A8193540			(983) 712-3074
8		A7688978			(972) 877-5079
9		A7654833			(983) 712-3074
10		A4895465			(983) 784-3553
11		A65498712			(984) 735-0571
12
13
14	Sheet1 Data:
15	Pho Nbr		IDS		Lookup:
16	(982) 061-6220		A4944571
17	(987) 920-1623		A2333895
18	(972) 877-5079		A6460856		MATCH
19	(983) 712-3074		A8193540		MATCH
20	(983) 784-3553		A4809077
21	(984) 735-0571		A880395
22	(985) 720-9968		A7688978
23	(977) 665-1027		A7654833
24	(987) 896-2723		A7488906
25	(982) 556-9905		A1252460
Sheet2

Cell Formulas
Range	Formula
E16:E25	E16	=IF(XLOOKUP(A16,$E$3:$E$11,$B$3:$B$11,"",0)=C16,"MATCH","")

D=fw:b]E16[/XD][XD]=IF(XLOOKUP(A16,$E$3:$E$11,$B$3:$B$11,"",0)=C16,"MATCH","")[/XD][/XR][/RANGE]

 

[Peter_SSs]

This is my take on it

ExcelBrent.xlsm
	B	C	D	E
1	ID Nbr			PhoneNbr
2	A2333895			9947292689
3	A6460856			9947292681
4	A8193540			9901372115
5	A6460856			9728775079
6	A8193540			9837123074
7	A7688978			9728775079
8	A7654833			9837123074
9	A4895465			9837843553
10	A65498712			9847350571
Sheet2

ExcelBrent.xlsm
	H	I	J	K	L	M
1	Pho Nbr				IDS	Result
2	9820616220				A4944571	A4999999
3	9879201623				A2333895	Not found
4	9728775079				A6460856	A7688978
5	9837123074				A8193540	MATCH
6	9837843553				A4809077	A4895465
7	9847350571				A880395	MATCH
8	9857209968				A7688978	Not found
Sheet1

Cell Formulas
Range	Formula
M2:M8	M2	=LET(ID,XLOOKUP(H2,Sheet2!E:E,Sheet2!B:B,"Not found"),IF(ID=L2,"MATCH",ID))

 

[ExcelBrent]

This worked perfectly! Thank you Peter_SSs

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.