Search for different values

[dascooper]

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.

 

[ShaneDevenshire]

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)

 

[T. Valko]

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)