lookup based on a partial, multiple matches

[goetzfridt]

Greetings,

Does anybody know how to do a lookup of several strings of characters
in cell? Kind of like an "=search" combined with a "=vlookup". Or can
anybody explain how to do a "=search" based on multiple text strings?

Take a look at my example below:

I appreciate any ideas anybody has. Thanks in advance...

Example:

A1: John
A2: Jerry
A3: Jim
A4: Mary
A5: Mary Jo

I want to find if either of the following character strings occur, and
if so return the related code:

A10: Jo B10: Code1
A11: Ma B11: Code2

In other words:

Look for "Jo" or "Mar" in A1. The result would be "Code1".
Look for "Jo" or "Mar" in A2. The result would be "N/A".
Look for "Jo" or "Mar" in A3. The result would be "N/A".
Look for "Jo" or "Mar" in A4. The result would be "Code2".
Look for "Jo" or "Mar" in A5. The result would be "Code1".

 

[Bob Phillips]

Try,

=IF(NOT(ISERROR(FIND("Jo",A1)>0)),"Code1",IF(NOT(ISERROR(FIND("Mar",A1)>0)),
"Code2",NA()))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

... or a bit more succinctly

=IF(NOT(ISERROR(FIND("Jo",A1))),"Code1",IF(NOT(ISERROR(FIND("Mar",A1))),"Cod
e2",NA()))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Try,

=IF(NOT(ISERROR(FIND("Jo",A1)>0)),"Code1",IF(NOT(ISERROR(FIND("Mar",A1)>0)),
"Code2",NA()))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)