How to return a value from a range if cell contains that value <p>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'.</p> <p>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.</p> <p>I hope this makes sense, and thank you in advanced :)</p>