If isnumber search

[kyleball]

Hi All!

I have 1000's of rows that I want to pull out if they say a particular name; the below works great:

=IF(ISNUMBER(SEARCH("Kyle Ball",Y2)),"Kyle Ball",IF(ISNUMBER(SEARCH("Peter Pan",Y2)),"Peter Pan",""))


I did this before for about 10 names - so it was quite manual until written.

The problem is - someone else has requested it, and it's about 50 names. What would be great is if I could search a table, e.g:

[TABLE="width: 184"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Contains[/TD]
[TD]Bring Back[/TD]
[/TR]
[TR]
[TD]Kyle Ball[/TD]
[TD]Kyle Ball[/TD]
[/TR]
[TR]
[TD]Peter Pan[/TD]
[TD]Peter Pan[/TD]
[/TR]
[TR]
[TD]Mary Poppins[/TD]
[TD]Mary Poppins
[/TD]
[/TR]
</tbody>[/TABLE]

Then I would not have to write each name individually, the formula would be shorter, and added names would be pain free.

Is this possible?

 

[steve the fish]

How about this with the names to return in sheet2 column A and if not found return "".

=IFERROR(VLOOKUP(A1,Sheet2!A:A,1,0),"")

 

[snb_]

Are you familiar with autofilter in Excel ?

 

[kyleball]

How about this with the names to return in sheet2 column A and if not found return "".

=IFERROR(VLOOKUP(A1,Sheet2!A:A,1,0),"")

Hi Steve,

Sorry I should have been more clear. The text does not just say the name, it has other things in it. Which is why I use the search formula above.

E.g. KYLE BALL/54245/EMPLOYEE SUBSISTENCE or 54245/Kyle Ball/T&E

Etc.

Are you familiar with autofilter in Excel ?

Unfortunately not - I will google it!


Just to make my original request more clear:

I currently use this formula:

=IF(ISNUMBER(SEARCH("Kyle Ball",Y2)),"Kyle Ball",IF(ISNUMBER(SEARCH("Peter Pan",Y2)),"Peter Pan",""))

To search a column with 1000's of rows; I have to search and return it because the cell contains more than just the name. The problem is I have around 50 names to do it with, so was wondering if I can search from a table, like A1:B50 or would I have to search a specific name and return name 50 times?

 

[steve the fish]

Have a go with this one:

=IF(ISERROR(FIND(Sheet2!A:A,A1)),"",Sheet2!A:A)

 

[steve the fish]

No that aint going to work is it?

 

[kyleball]

Unfortunately not, no...

I guess there isn't a way to do it as I described!

 

[steve the fish]

There may be there may not be. Im not skilled enough to think of a way. Is there consistency in the extra wording?

You said:

KYLE BALL/54245/EMPLOYEE SUBSISTENCE or 54245/Kyle Ball/T&E

Are they all like this? Possible to extract kyle ball from these easily enough using the /