I have a list of Names (First and Surname) in A:A. I also have a range called 'Surnames'. How can I apply a formula so that B:B returns the surname only of A:A if that name is found in the range 'Surnames'.
I other words, I want to check a cell in A1, if part of this cell value contains a name listed in my range of surnames, return the surname that A1 include in B1.
I hope this makes sense, and thank you in advanced :)
Try this formula in B1 copied down
=IF(COUNTIF(Surnames,REPLACE(A1,1,FIND(" ",A1),"")),REPLACE(A1,1,FIND(" ",A1),""),"")
The REPLACE/FIND part extracts the surname from A1 (assuming A1 is in the form <firstname><space><surname>) and then the formula uses COUNTIF to count that surname in your named range. If it's there the surname is returned, otherwise you get a blank.
There may be shorter ways but this should ensure a match with the surname, otherwise you might get "false positives" if part of the surname (or the firstname) might match a surname
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments