search cell from list of possibilities

[elmetal]

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

 

[Jonmo1]

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]

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]

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]

ADVERTISEMENT

Here's a UDF solution:

Excel Workbook
	A	B	C
1	LISTOFSTUFF	STUFF	Which
2	This is a dog	Horse	Dog
3	This is cat's house	Dog	Cat
4	Horses love this place	Cat	Horse
5		Chicken	#N/A
Sheet10

The UDF is:

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]

thank you guys! Works great

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[dulitul]

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.