Partial lookup based on multiple criteria

[goetzfridt]

Greetings,

Does anybody know how to do a lookup of several strings of characters
in a cell? Kind of like an "=search" combined with a "=vlookup". I
know how to enter an vlookup that is based on multiple criteria, but I
do not know how to do the same thing with "=search" function.

For example, from the following list:

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

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

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

In other words:

Look for "Jo" or "Mar" in A1. This would return "Code1"
Look for "Jo" or "Mar" in A2. This would return "N/A"
Look for "Jo" or "Mar" in A3. This would return "N/A"
Look for "Jo" or "Mar" in A4. This would return "Code2"
Look for "Jo" or "Mar" in A5. This would return "Code1"

 

[J.E. McGimpsey]

one way:

=IF(ISERR(FIND($A$10, A1)), IF(ISERR(FIND($A$11, A1)), "NA",
$B$11), $B$10)