Nov 07 2023 12:49 PM
I have two sheets with first and last names. I'm trying to have a 3rd column validate whether the names are matching. If a match to say match but if not be blank. As an aside, the first name spelling can vary. For instance Robert vs. Rob, but the last name Putnam is the same, so I want it to be a 'match'
I tried xlookup and If formulas and I still can't seem to get it right. Any insight is greatly appreciated.
1) =XLOOKUP(A2,Nomi!$B:$B,XLOOKUP(B2,Nomi!$C:$C,Nomi!$B:$B,"-",0))
---I get an error message on this. Please note, this doesn't account for the first name and not working with wildcards...
2) IF(COUNTIFS(Humanities!B:B,A2,Humanities!C:C,B2),"Yes","No")
---- Problem with this one, is I can't get 'B2' to match when the name is Rob instead of Robert and vice versa
Thank you!
Nov 07 2023 11:07 PM
SolutionTo achieve your desired result of identifying matches in first and last names with some flexibility, you can use the following formula:
=IF(COUNTIFS(Nomi!$B:$B, A2, Nomi!$C:$C, B2)+COUNTIFS(Nomi!$B:$B, LEFT(A2,3)&"*", Nomi!$C:$C, B2)+COUNTIFS(Nomi!$B:$B, A2, Nomi!$C:$C, LEFT(B2,3)&"*")+COUNTIFS(Nomi!$B:$B, LEFT(A2,3)&"*", Nomi!$C:$C, LEFT(B2,3)&"*"),"Yes","No")
This formula checks for matches in the first names and last names, as well as variations with the first name (using a wildcard "*"). This should work even when the first name is a variation like "Rob" or "Robert." If a match is found in any of these conditions, it returns "Yes," otherwise, it returns "No."
Make sure to adjust the ranges (Nomi!$B:$B and Nomi!$C:$C) to match the actual ranges in your sheets.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Nov 08 2023 08:29 AM
Nov 07 2023 11:07 PM
SolutionTo achieve your desired result of identifying matches in first and last names with some flexibility, you can use the following formula:
=IF(COUNTIFS(Nomi!$B:$B, A2, Nomi!$C:$C, B2)+COUNTIFS(Nomi!$B:$B, LEFT(A2,3)&"*", Nomi!$C:$C, B2)+COUNTIFS(Nomi!$B:$B, A2, Nomi!$C:$C, LEFT(B2,3)&"*")+COUNTIFS(Nomi!$B:$B, LEFT(A2,3)&"*", Nomi!$C:$C, LEFT(B2,3)&"*"),"Yes","No")
This formula checks for matches in the first names and last names, as well as variations with the first name (using a wildcard "*"). This should work even when the first name is a variation like "Rob" or "Robert." If a match is found in any of these conditions, it returns "Yes," otherwise, it returns "No."
Make sure to adjust the ranges (Nomi!$B:$B and Nomi!$C:$C) to match the actual ranges in your sheets.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.