Forum Discussion
Xlookup multiple variables or If Counts?
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.
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.
- Thank you!, made a slight modification to the formula and it worked just fine!
