Forum Discussion

Clare1487's avatar
Clare1487
Copper Contributor
Dec 19, 2022

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

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
Formulas and Functions

3 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 19, 2022

    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.

    • Clare1487's avatar
      Clare1487
      Copper Contributor
      Dec 19, 2022

      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 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 19, 2022

    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.

     

Resources