That's fantastic.

Please explain the 17^17 part, what does 17 refer to?

I have extended the search range of the formula =LOOKUP(17^17,SEARCH("*."&Sheet2!$A$1:$A$500&"*",C4),Sheet2!$B$1:$B$500)

How do I make it return a blank if it doesn't find a relevant search field? i.e. not #N/A. normally I put ,"" at the end.

Can I apply this to First Name, company name etc?

What if I have Andrew, Andre, Andrea. I will sort them alphabetically. Will it return Andre for Andrew?

You're a star!