Dec 19 2022 04:56 AM
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 |
Dec 19 2022 05:25 AM
=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.
Dec 19 2022 05:31 AM
=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.
Dec 19 2022 05:39 AM
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.