Finding names in a list of names

**djchapple** · 09-01-2011, 02:39 PM

I have an excel spreadsheet with several clumns, two of them (say C and D) contain two or three names.

Colums C and D may contains names such as

Joe Smith
Joe Smith & Fred Blogs
Joe Smith and Fred Blogs
Joe and Sue Bloggs
Joe Smith, Fred Bloggs and Bill Brown

As you can see there is no real set pattern to the data input.

I would like to find all cells in (say) Column C containing (say) Joe Smith. I am using the excel the Excel" Find" forumla.

In (say) D6 I enter "=FIND("Joe",C6,1)
In (say) E6 I enter "=FIND("Smith",C6,1)

So D6 holds either "#Value!" if "Joe" is not found and a "number" if it is.
And E6 holds either "#Value!" if "Smith" is not found and a "number" if it is.

If both D6 and E6 both contain a number "Joe Smith" has been found and I want F6 to hold (say) the number 1

If either D6 or E6 contains "#Value!" then "Joe Smith" has not been found and I want F6 to be blank.

How can I create the values needed in column F6?

**mrice** · 09-01-2011, 02:42 PM

Why not try..

=FIND("Joe Smith",C6,1)

**yay_excel** · 09-01-2011, 02:46 PM

In F6: =IF(AND(D6>0,E6>0),D6,"")

This formula will return the number in D6 if both D6 and E6 contain numbers or nothing if they do not both contain numbers.

**djchapple** · 09-02-2011, 10:16 AM

Originally Posted by yay_excel

In F6: =IF(AND(D6>0,E6>0),D6,"")

This formula will return the number in D6 if both D6 and E6 contain numbers or nothing if they do not both contain numbers.

This is a great idea under normal circumstances but the "text" stored in these two cells is #VALUE! and is the output from the FIND function.

If I use the AND function as suggested the it outputs and error message when it meets #VALUE!. Obviously #VALUE! is not stored as text.

**ron2k_1** · 09-02-2011, 10:32 AM

Assuming that I'm correct in that your problem now is an error handler you can change your formula to this:
=IF(ISERROR(D6*E6),"",IF(AND(D6>0,E6>0),D6,""))

**djchapple** · 09-04-2011, 11:14 AM

Originally Posted by ron2k_1

Assuming that I'm correct in that your problem now is an error handler you can change your formula to this:
=IF(ISERROR(D6*E6),"",IF(AND(D6>0,E6>0),D6,""))

Thanks for puttimg on the right track. With 2020 the function i, I believe, IFERROR