Is there a method to perform the following function?:
I) Using a list of values (In my case, these are text. For the discussion,
lets assume they are colors in 3 place format, like BLK, BLU, BRN, GRN, GRY,
RED, WHT, etc.).
II) Look in a cell, and see if any of those values exist (NOTE: There will
never be more than one of these values within a cell).
III) If any one of these values is found within the cell, state which value
it is.

For example, the statement might look like this:
=IF(FIND({any of the values listed},E2,1),{whatever value it found in
E2},€�Sorry Charlie€�) - where E2 is the €œWithin Text€� location for the FIND
function.

I can accomplish this with nested IF functions, but this limits me to only
seven values.

Jim J. wrote...
Is there a method to perform the following function?:
I) Using a list of values (In my case, these are text. For the discussion,
let's assume they are colors in 3 place format, like BLK, BLU, BRN, GRN, GRY,
RED, WHT, etc.).
II) Look in a cell, and see if any of those values exist (NOTE: There will
never be more than one of these values within a cell).
III) If any one of these values is found within the cell, state which value
it is.
....

=LOOKUP(2,1/COUNTIF(YourCellHere,"*"&YourListHere&"*"),YourLis tHere)

Try this UDF:

Function ColorMe(r As Range) As String
s = Array("BLK", "BLU", "BRN", "GRN", "GRY", "RED", "WHT")
ColorMe = "Sorry Charlie"
For i = LBound(s) To UBound(s)
If InStr(r.Value, s(i)) Then
ColorMe = s(i)
Exit Function
End If
Next
End Function

--
Gary's Student


"Jim J." wrote:

Is there a method to perform the following function?:
I) Using a list of values (In my case, these are text. For the discussion,
lets assume they are colors in 3 place format, like BLK, BLU, BRN, GRN, GRY,
RED, WHT, etc.).
II) Look in a cell, and see if any of those values exist (NOTE: There will
never be more than one of these values within a cell).
III) If any one of these values is found within the cell, state which value
it is.

For example, the statement might look like this:
=IF(FIND({any of the values listed},E2,1),{whatever value it found in
E2},€�Sorry Charlie€�) - where E2 is the €œWithin Text€� location for the FIND
function.

I can accomplish this with nested IF functions, but this limits me to only
seven values.

You can avoid VBA if the colors are standalone:
=IF(OR(A1="RED",A1="BLK",A1="BLU",A1="BRN",A1="GRN ",A1="GRY",A1="WHT"),A1,"sorry charlie")

--
Gary''s Student


"Jim J." wrote:

Is there a method to perform the following function?:
I) Using a list of values (In my case, these are text. For the discussion,
lets assume they are colors in 3 place format, like BLK, BLU, BRN, GRN, GRY,
RED, WHT, etc.).
II) Look in a cell, and see if any of those values exist (NOTE: There will
never be more than one of these values within a cell).
III) If any one of these values is found within the cell, state which value
it is.

For example, the statement might look like this:
=IF(FIND({any of the values listed},E2,1),{whatever value it found in
E2},€�Sorry Charlie€�) - where E2 is the €œWithin Text€� location for the FIND
function.

I can accomplish this with nested IF functions, but this limits me to only
seven values.

try this idea. Fill in your needs.

=IF(COUNT(IF($C$7={"blu","blk"},1,"")),"Y","N")
or
=IF(COUNT(IF($C$7={"blu","blk"},1,"")),$c$7,"")
--
Don Guillett
SalesAid Software

"Jim J." wrote in message
...
Is there a method to perform the following function?:
I) Using a list of values (In my case, these are text. For the
discussion,
let's assume they are colors in 3 place format, like BLK, BLU, BRN, GRN,
GRY,
RED, WHT, etc.).
II) Look in a cell, and see if any of those values exist (NOTE: There will
never be more than one of these values within a cell).
III) If any one of these values is found within the cell, state which
value
it is.

For example, the statement might look like this:
=IF(FIND({any of the values listed},E2,1),{whatever value it found in
E2},"Sorry Charlie") - where E2 is the "Within Text" location for the
FIND
function.

I can accomplish this with nested IF functions, but this limits me to only
seven values.

Gary''s Student wrote...
You can avoid VBA if the colors are standalone:

=IF(OR(A1="RED",A1="BLK",A1="BLU",A1="BRN",A1="GR N",A1="GRY",A1="WHT"),A1,
"sorry charlie")
....

Ugh! Brute force and entire cell content matching can be shortened to

=IF(OR(A1={"RED","BLK","BLU","BRN","GRN","GRY","WH T"}),A1,"not found")

Excuse for interrupting

Gary''s Student, maybe you can help me again for VBE, my today's ? post "SP
UDF for Excel in Excel Programming " - this one is for New Year's sake
....thanks from dribler2
http://www.microsoft.com/office/comm...g=en&cr=US&p=1
sign off...

You are correct Harlan. This is the better formula, both compact and much
easier to expand. I'll file this away.
--
Gary's Student


"Harlan Grove" wrote:

Gary''s Student wrote...
You can avoid VBA if the colors are standalone:

=IF(OR(A1="RED",A1="BLK",A1="BLU",A1="BRN",A1="GR N",A1="GRY",A1="WHT"),A1,
"sorry charlie")
....

Ugh! Brute force and entire cell content matching can be shortened to

=IF(OR(A1={"RED","BLK","BLU","BRN","GRN","GRY","WH T"}),A1,"not found")

Neater than my solution.

--
Don Guillett
SalesAid Software

"Gary''s Student" wrote in message
...
You can avoid VBA if the colors are standalone:
=IF(OR(A1="RED",A1="BLK",A1="BLU",A1="BRN",A1="GRN ",A1="GRY",A1="WHT"),A1,"sorry
charlie")

--
Gary''s Student


"Jim J." wrote:

Is there a method to perform the following function?:
I) Using a list of values (In my case, these are text. For the
discussion,
let's assume they are colors in 3 place format, like BLK, BLU, BRN, GRN,
GRY,
RED, WHT, etc.).
II) Look in a cell, and see if any of those values exist (NOTE: There
will
never be more than one of these values within a cell).
III) If any one of these values is found within the cell, state which
value
it is.

For example, the statement might look like this:
=IF(FIND({any of the values listed},E2,1),{whatever value it found in
E2},"Sorry Charlie") - where E2 is the "Within Text" location for the
FIND
function.

I can accomplish this with nested IF functions, but this limits me to
only
seven values.

Maybe I misstated my needs. Lets try again:
I need to:
- Look into the contents of a cell, ex.: A12 (NOTE: Cell contents may be
100 characters)
- What I am looking for is any one of 5 values that I have listed in another
worksheet
- If I find any one of those values in A12 (will only be one of the 5
values), I want the function to report what value it found.
So, if my lookup values are €œBLK€�, €œBLU€�, €œGRN€�, €œRED€�, €œYEL€�, and if the
contents of A12 is: 782517 0010 Plnd I18-9BLK-002 01/02 18 Awg, .070 Dia .009
5.4646
then I want the function to tell me it found €œBLK€�

Thanks in advance!

"Gary''s Student" wrote:

You can avoid VBA if the colors are standalone:
=IF(OR(A1="RED",A1="BLK",A1="BLU",A1="BRN",A1="GRN ",A1="GRY",A1="WHT"),A1,"sorry charlie")

--
Gary''s Student


"Jim J." wrote:

Is there a method to perform the following function?:
I) Using a list of values (In my case, these are text. For the discussion,
lets assume they are colors in 3 place format, like BLK, BLU, BRN, GRN, GRY,
RED, WHT, etc.).
II) Look in a cell, and see if any of those values exist (NOTE: There will
never be more than one of these values within a cell).
III) If any one of these values is found within the cell, state which value
it is.

For example, the statement might look like this:
=IF(FIND({any of the values listed},E2,1),{whatever value it found in
E2},€�Sorry Charlie€�) - where E2 is the €œWithin Text€� location for the FIND
function.

I can accomplish this with nested IF functions, but this limits me to only
seven values.

Maybe I misstated my needs. Lets try again:
I need to:
- Look into the contents of a cell, ex.: A12 (NOTE: Cell contents may be
100 characters)
- What I am looking for is any one of 5 values that I have listed in another
worksheet
- If I find any one of those values in A12 (will only be one of the 5
values), I want the function to report what value it found.
So, if my lookup values are €œBLK€�, €œBLU€�, €œGRN€�, €œRED€�, €œYEL€�, and if the
contents of A12 is: 782517 0010 Plnd I18-9BLK-002 01/02 18 Awg, .070 Dia .009
5.4646
then I want the function to tell me it found €œBLK€�

Thanks in advance!


"Harlan Grove" wrote:

Gary''s Student wrote...
You can avoid VBA if the colors are standalone:

=IF(OR(A1="RED",A1="BLK",A1="BLU",A1="BRN",A1="GR N",A1="GRY",A1="WHT"),A1,
"sorry charlie")
....

Ugh! Brute force and entire cell content matching can be shortened to

=IF(OR(A1={"RED","BLK","BLU","BRN","GRN","GRY","WH T"}),A1,"not found")

Jim J. wrote...
Maybe I misstated my needs. Let's try again:
....

Dunno. Did you bother to try the formula

=LOOKUP(2,1/COUNTIF(YourCellHere,"*"&YourListHere&"*"),YourLis tHere)

where YourListHere could be either a defined name or an array constant
like

{"BLK","BLU","GRN","RED","YEL"}

or a reference to a range containing these values in separate cells?
That is, a formula like

=LOOKUP(2,1/COUNTIF(A12,"*"&{"BLK","BLU","GRN","RED","YEL"}&"* "),
{"BLK","BLU","GRN","RED","YEL"})