# search cell from list of possibilities

#### elmetal

##### New Member
Hello,

I have a list (let's say B2:B10) of things.
 LISTOFSTUFF STUFF This is a dog Horse This is cat's house Dog Horses love this place Cat Chicken

<tbody>
</tbody>

Now, what I want is a 3rd column that will look in column A and output which item in column B is present in that row. for example

 LISTOFSTUFF STUFF Which This is a dog Horse dog This is cat's house Dog cat Horses love this place Cat horse Chicken

<tbody>
</tbody>

that make sense? basically I need it to search the A column for the presence of each item in column B and output which is present.

To keep things simple, let's say only 1 of each item in column b can be present in any value of column A

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Jonmo1

##### MrExcel MVP
Welcome to the board.

Try
=LOOKUP(2^15,SEARCH(\$B\$2:\$B\$10,A2),\$B\$2:\$B\$10)

There cannot be any blanks in B2:B10.
If you really only have 4 items in STUFF, then change it to B2:B5

#### oldbrewer

##### Well-known Member
will column A always be a short sentence
do you wish to search column B to match the animal in the sentence in say cell A1

#### elmetal

##### New Member
exactly. I used animals as an example. What I need is to search column A for any of column B contents, and if present, output which is present (let's assume only 1 can be present per cell, or none)

what I actually have is a spreadsheet of over 35000 rows all of which may or may not have a word in the list I have made up

#### JoeMo

##### MrExcel MVP
Here's a UDF solution:
Excel Workbook
ABC
1LISTOFSTUFFSTUFFWhich
2This is a dogHorseDog
3This is cat's houseDogCat
4Horses love this placeCatHorse
5Chicken#N/A
Sheet10

The UDF is:
Code:
``````Function Which(rCell As Range, rStuff As Range)
Dim vA As Variant
vA = Split(rCell, " ")
For i = LBound(vA) To UBound(vA)
For j = 1 To rStuff.Rows.Count
If InStr(LCase(vA(i)), LCase(rStuff.Cells(j, 1))) Then
Which = rStuff.Cells(j, 1).Value
Exit Function
End If
Next j
Next i
Which = CVErr(xlErrNA)
End Function``````

#### elmetal

##### New Member
thank you guys! Works great

#### Jonmo1

##### MrExcel MVP
Glad to help, thanks for the feedback.

#### dulitul

##### Board Regular
Hey,

You dont need vba for that.

Code
=IF(COUNT(SEARCH("*cat*",C1,1)),"cat",IF(COUNT(SEARCH("*Dog*",C1)),"dog",IF(COUNT(SEARCH("*horse*",C1)),"horse",IF(COUNT(SEARCH("*chicken*",C1)),"chicken",""))))

Replace the animals in the formula of whatever you search and also C1 with the cell respective you want to search from.

Last edited:

Replies
3
Views
338
Replies
1
Views
206
Replies
0
Views
300
Replies
3
Views
228
Replies
1
Views
247

1,195,855
Messages
6,011,974
Members
441,661
Latest member
Pammie007

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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