Xlookup multiple variables or If Counts?

[YY234]

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!

[#2 · Accepted solution]

@YY234 

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

[#3]

Thank you!, made a slight modification to the formula and it worked just fine!