Check if a cell contains text compared to a range of cells

[GD1226, Captain Ahab, manface]

I'm trying to see if a single cell contains the same text as any
particular cell within a large range of cells. For instance if i have
a column with: (picture this is the first column)

Baseball Bat
Football Shoes
Baseball Glove
Soccer Ball
Golf Club
Turkey Sandwich

and a range of cells that have: (b column)

Baseball
Footbal
Soccer
Golf

I want the C column to return

Baseball
Football
Baseball
Soccer
Golf
#N/A

Is this possible?

Thanks!

-Anthony

 

[Max]

One way ..

Source range in A1:A6
Base items to be compared listed in B1:B4

In C1:
=IF(B1="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,$A$1:$A$6)))>0,B1,""))
Copy down to C4 (to last row of data in col B)

 

[GD1226, Captain Ahab, manface]

One way ..

Source range in A1:A6
Base items to be compared listed in B1:B4

In C1:
=IF(B1="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1,$A$1:$A$6)))>0,B1,""))
Copy down to C4 (to last row of data in col B)

Thanks for the response. I tried that formula and it returns:

C1: Baseball
C2: Footbal
C3: Soccer
C4: Golf
C5: <blank>
C6: <blank>

Basically, it's returning the base comparison items instead of looking
it up and finding which to print.

Any other ideas? I really appreciate the help

 

[Max]

Sorry, think I mis-read it earlier ..

Put this in C1, array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=INDEX($B$1:$B$4,MATCH(TRUE,ISNUMBER(SEARCH($B$1:$B$4,A1)),0))
Copy C1 down to the extent of data in col A. This will return the results
that you seek,

 

[GD1226, Captain Ahab, manface]

Sorry, think I mis-read it earlier ..

Put this in C1, array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=INDEX($B$1:$B$4,MATCH(TRUE,ISNUMBER(SEARCH($B$1:$B$4,A1)),0))
Copy C1 down to the extent of data in col A. This will return the results
that you seek,

Awesome! works like a charm. thanks!

 

[Max]

You're welcome.
Glad that did it here.

 

[Simon G]

Hi. I have a similar task. If column A contains a list of symptoms say, and the next column (B) contains more details of about those symptoms , what I would like to be able to do is search column A for the text e.g. 'headache' and then in Column C, whereever Column A says 'headache', it uses Column B to give me all the different types of headache. So A1 might be headache, B1 might be mild, A2 might be rash, B2 might be moderate, A3 might be headache, but B3 might be severe. In column C, I want to display all the different types or detail of the headache, if column A contains the word headache. Any help would be most appreciated. Many thanks. S.