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

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

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

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"GD1226, Captain Ahab, manface" wrote in message
oups.com...
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

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

On Apr 11, 7:36 pm, "Max" wrote:
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)
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---
"GD1226, Captain Ahab, manface" wrote in ooglegroups.com...

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

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

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

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,
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"GD1226, Captain Ahab, manface" wrote
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

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

On Apr 11, 9:48 pm, "Max" wrote:
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,
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---
"GD1226, Captain Ahab, manface" wrote

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

Awesome! works like a charm. thanks!

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

You're welcome.
Glad that did it here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"GD1226, Captain Ahab, manface" wrote
Awesome! works like a charm. thanks!