# If isnumber search

#### kyleball

##### New Member
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:

 Contains Bring Back Kyle Ball Kyle Ball Peter Pan Peter Pan Mary Poppins Mary Poppins

<colgroup><col span="2"></colgroup><tbody>
</tbody>

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?

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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

Are you familiar with autofilter in Excel ?

=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?

Have a go with this one:

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

No that aint going to work is it?

Unfortunately not, no...

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

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 /

Replies
0
Views
422
Replies
3
Views
653
Replies
1
Views
385
Replies
6
Views
532
Replies
2
Views
315

1,203,601
Messages
6,056,212
Members
444,850
Latest member
dancasta7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back