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

Copper Contributor

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.



SCDate ADate
321611/01/2022 21611/01/2022
634611/01/2022 69611/01/2022
1109111/01/2022 73111/01/2022
1676611/01/2022 136111/01/2022
2723611/01/2022 226611/01/2022
3300611/01/2022 290111/01/2022
3669111/01/2022 613111/01/2022
4629611/01/2022 622111/01/2022
4722111/01/2022 666111/01/2022
5020611/01/2022 762111/01/2022
3 Replies


=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.

matching duplicates.JPG 


=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.

match dates.JPG

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.