Search for different values
 

I want to be able to reference a cell that has text separated by comma's, and
place a value in the neighboring cell if one of several values is found:

ie, list of values could be: BA, GO, GOP, IA, LSE, RC, RRO, TO, TOP, TSP

and I would want to identify it if it contains one of the following : GO,
GOP, LSE, TO

I am thinking that using the Search function within a bunch of nested IF
statements might work, but though I would put this out there to see if there
were some more intelligent solutions.

Search for different values
 

Hi,

The easiest way would be to enter the individual items you want to check for
in a range like G1:G4 and with the thing you want to look at in A1

=SEARCH(G1:G4,A1)
--
Thanks,
Shane Devenshire


"dascooper" wrote:

I want to be able to reference a cell that has text separated by comma's, and
place a value in the neighboring cell if one of several values is found:

ie, list of values could be: BA, GO, GOP, IA, LSE, RC, RRO, TO, TOP, TSP

and I would want to identify it if it contains one of the following : GO,
GOP, LSE, TO

I am thinking that using the Search function within a bunch of nested IF
statements might work, but though I would put this out there to see if there
were some more intelligent solutions.

Search for different values
 

What kind of result do you want?

This array formula** will return TRUE if any of the variables are found
within the string.

List the variables in a range of cells:

X1 = GO
X2 = GOP
X3 = LSE
X4 = TO

=OR(ISNUMBER(SEARCH(X1:X4&",",A1&",")))

If *any* cell in the range X1:X4 is empty the formula will return TRUE even
if none of the other entries match. You can account for that by using this
array** version:

=OR(ISNUMBER(SEARCH(IF(G1:G4="",1,G1:G4&","),A1&", ")))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"dascooper" wrote in message
...
I want to be able to reference a cell that has text separated by comma's,
and
place a value in the neighboring cell if one of several values is found:

ie, list of values could be: BA, GO, GOP, IA, LSE, RC, RRO, TO, TOP, TSP

and I would want to identify it if it contains one of the following : GO,
GOP, LSE, TO

I am thinking that using the Search function within a bunch of nested IF
statements might work, but though I would put this out there to see if
there
were some more intelligent solutions.