Matching duplicates in two sets of columns excel version 2211 in office 365

[Clare1487]

I need to search for a matching reference number (looking for A2 in column D) and then see if the date (column E) is the same as that of the first (B2)

I have tried using XLOOKUP as a nested rule but keep getting an error returned.  Column D has over 7,000 entries and there are duplicates within columns A and D themselves so unable to use a simple conditional formatting rule.

SC	Date		A	Date
3216	11/01/2022		216	11/01/2022
6346	11/01/2022		696	11/01/2022
11091	11/01/2022		731	11/01/2022
16766	11/01/2022		1361	11/01/2022
27236	11/01/2022		2266	11/01/2022
33006	11/01/2022		2901	11/01/2022
36691	11/01/2022		6131	11/01/2022
46296	11/01/2022		6221	11/01/2022
47221	11/01/2022		6661	11/01/2022
50206	11/01/2022		7621	11/01/2022

[#2]

@Clare1487 

=IFNA(IF(VLOOKUP(A2,$D$2:$E$25,2,FALSE)=B2,"TRUE","no match"),"no match")

For older versions of Excel you can try this formula.

 

[#3]

@Clare1487 

=IFNA(IF(XLOOKUP(A2,$D$2:$D$25,$E$2:$E$25)=B2,"TRUE","no match"),"no match")

With XLOOKUP you can try this formula.

=BYROW(A2:A20,LAMBDA(row,IFNA(IF(XLOOKUP(row,D2:D20,E2:E20)=OFFSET(row,0,1),"TRUE","no match"),"no match")))

If you want to spill the result you can try BYROW and LAMBDA.

[#4]

Thank you so much for all your input.  Unfortunately I'm not getting matches or "true" on a small sample I made to test them.  I will need to look at this later due to other commitments now, but didn't want to not respond after your kind help.

@OliverScheurich