Return cell content if a cell contains part of of text from another cell

[andranos]

Hello everyone, I'm currently looking a way to find a cell value if a cell contains part of of text from another cell
Might be easier for me to explain with image below so here we go

A	B	C	D
Chicken, Beef, Lamb	Meat	Boiled Eel	Fish
Orange, Apple, Banana	Fruit	Fried Chicken	Meat
Trout, Eel	Fish	Stir fry Broccoli	Veggie
Cabbage, Carrot, Broccoli	Veggie	Banana Split	Fruit

So basically the value from column D will be based on column C. If Column C contains word from Column A for example Boiled Eel contains Eel which is next to Fish from Column B, value for Column D would be Fish and so on

Thank you very much in advance guys!

 

[Crystalyzer]

There may be a formula that can do this, but it escapes me. I was able, however, to create a user defined function to do what you want. See below.

Book7
	A	B	C	D
1
2	Chicken, Beef, Lamb	Meat	Boiled Eel	Fish
3	Orange, Apple, Banana	Fruit	Fried Chicken	Meat
4	Trout, Eel	Fish	Stir fry Broccoli	Veggie
5	Cabbage, Carrot, Broccoli	Veggie	Banana Split	Fruit
6	Another One	Something	Two	Not Found
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=SplitLookup($A$2:$B$6,C2)

Public Function SplitLookup(LkupRange As Range, LkupValue As Range) As String
    Dim c As Range
    Dim NumWords As Long
    Dim v As String
    Dim i As Long, j As Long, t As Long
    
    If LkupRange.Columns.Count <> 2 Then
        SplitLookup = "#ERROR! Lookup Range must contain ONLY two columns"
        Exit Function
    End If
    
    If LkupValue.Columns.Count <> 1 Or LkupValue.Rows.Count <> 1 Then
        SplitLookup = "#ERROR! Lookup Value must contain ONLY one cell."
        Exit Function
    End If
    
    If LkupValue = "" Then
        SplitLookup = "#ERROR! Lookup Value cannot be blank."
        Exit Function
    End If
    
    NumWords = Len(LkupValue) - Len(Replace(LkupValue, " ", "")) + 1
    
    j = 1
    
    For i = 1 To NumWords
        t = InStr(j + 1, LkupValue, " ") - 1
        If t = -1 Then t = Len(LkupValue)
        v = Trim(Mid(LkupValue, i + j - 1, t))
        For Each c In LkupRange
            If InStr(1, c, v) > 0 Then
                SplitLookup = c.Offset(0, 1)
                Exit Function
            End If
        Next c
        j = InStr(j + 1, LkupValue, " ")
    Next i
    
    If SplitLookup = "" Then SplitLookup = "Not Found"
    
End Function

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

If your lists are not too long and IF you have the TEXTJOIN function (hence my previous post) you could try this formula approach.

andranos.xlsm
	A	B	C	D
1
2	Chicken, Beef, Lamb	Meat	Boiled Eel	Fish
3	Orange, Apple, Banana	Fruit	Fried Chicken	Meat
4	Trout, Eel	Fish	Stir fry Broccoli	Veggie
5	Cabbage, Carrot, Broccoli	Veggie	Banana Split	Fruit
6			Boiled Eel and Trout	Fish
7			Baked Potato
8			Poached Chicken with Orange Sauce	Meat, Fruit
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=TEXTJOIN(", ",1,UNIQUE(IFNA(INDEX(B$2:B$5,MATCH("*, "&FILTERXML("<p><c>"&SUBSTITUTE(C2," ","</c><c>")&"</c></p>","//c")&",*",", "&A$2:A$5&",",0)),"")))