Thread: How to search 2 columns and output a match in the 3rd

I have Excel 2010 and am trying to do the following:

Column A has free form data. Typically it will be last name, first name (i.e. smith, peter) but it may also have just text ("Accounting Dept Support")
Column B has just the last name ("smith")

I want to search Column A for any matches and output the match in Column C. any ideas on how to get started with this?

Hi

This will find the first instance of a match assuming data is in the range A1:A5.

Array enter (ctrl, shift, enter) the formula

=INDEX(A:A,MIN(IF(ISNUMBER(SEARCH("*"&B1&"*",A1:A5)),ROW(A1:A5),"")))

What do you want to do if there is more than 1 match? Or if there are no matches?

rylo