• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

Compare text in one cell against a range

Hi guys, I need a formula that will look at cell A1 and compare it against column B, if it finds it, show that value, if not, show "not found".

Cell A1 contains clean text, but column B contains more than just the text I'm looking for.

For example,
A1: John Smith

B1: Customer Name, Michael Onslow of company A
B2: Customer Name, Peter Taylor of company L
B3: Customer Name,  John Smith of company D
B4: Customer Name, Amanda Ormond of company W

So in cell C1 I need it to look at A1, find it in column B and display the contents of that cell in column B.

Hope that makes sense.
1 Solution
Rory ArchibaldCommented:
=IF(ISNUMBER(MATCH("*"&A1&"*",B:B,0)),VLOOKUP("*"&A1&"*",B:B,1,FALSE),"Not found")

if using 2007 or later you can shorten to:

=IFERROR(VLOOKUP("*"&A1&"*",B:B,1,FALSE),"Not found")
amaru96Author Commented:

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now