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

  • Thread starter Thread starter GD1226, Captain Ahab, manface
  • Start date Start date
G

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
 
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)
 
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)
Click to expand...

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
 
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,
 
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,
Click to expand...

Awesome! works like a charm. thanks!
 
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.
 
Back
Top