Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SEARCH A NAME ON COL A & SUM THE VALUES ON COL B | Excel Worksheet Functions | |||
allow search on ranges of values | Excel Worksheet Functions | |||
Search multiple values to return single values | Excel Worksheet Functions | |||
search after values | Excel Discussion (Misc queries) | |||
search after values | Excel Discussion (Misc queries) |