Another way:

=LOOKUP(2,1/(ISNUMBER(SEARCH(A1:A10,AC2))),A1:A10)

If more than 1 name is in the cell the formula will match the last one:

AC2 = Jon talked to Tom

Formula result = Tom

Biff

"Toppers" wrote in message
...
Alternative ...

Set names (jon,bill etc) in column A and numbers 1,2 etc in column B

A B
jon 1
bill 2
tom 3
etc

Use the following formula:

=INDEX($A$1:$A$10,SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$10,AC2)))*$B$1:$B$10))

"Elkar" wrote:

Yes, Excel is limited to 7 "layers" of nesting. Here's an alternative
approach to your formula:

=IF(ISNUMBER(SEARCH("jon",AC2)),"jon","") &
IF(ISNUMBER(SEARCH("bill",AC2)),"bill","") &
IF(ISNUMBER(SEARCH("tom",AC2)),"tom","") & etc....

HTH,
Elkar


"Jonathan Horvath" wrote:

is there a limit to the number of nests you can have in a nested
IF(ISNUMBER(SEARCH)) functions?

For example, I have one that works properly with 6; for example:

=IF(ISNUMBER(SEARCH("jon",AC2)),"jon",IF(ISNUMBER( SEARCH("bill",AC2)),"bill",IF(ISNUMBER(SEARCH("tom ",AC2)),"tom",IF(ISNUMBER(SEARCH("joe",AC2)),"joe" ,IF(ISNUMBER(SEARCH("rob",AC2)),"rob",IF(ISNUMBER( SEARCH("ted",AC2)),"ted",""))))))


if I add another nest, Excel finds an error with the last search and
will
not allow it...

Thanks in advance for your help,

Jonathan