Combine Search and Vlookup?

[Gaveon]

Hello,

Is there a way i can use the search function to refer to another table?
I have an export of various file names and need to search inside for various words.
Example fields
20200727 Apple 1111
20200727 Orange 1111
20200727 Apple 2222
I want to search if cell contains Apple If so displays the word "Apple"
If False, search for the word "orange", and display orange.
But i need to do this with a large number of different word search
Ive managed to get the formula to work by nesting IF statements but its very long list (people not using same naming protocols sigh...)
=IF(ISNUMBER(SEARCH("apple",[@[Document Name]])), "Apple",IF(ISNUMBER(SEARCH("orange",[@[Document Name]])), "orange",.........
Is there a way i could shorten the formula and make it easier to add more searches, maybe referring to a vlookup or array?

Thank you,

 

[jasonb75]

Not tested, with list of words in A2:A10 (adjust as needed), try this one

=LOOKUP(1e+100,SEARCH($A$2:$A$10,[@[Document Name]]),$A$2:$A$10)

Note that if it does work then some changes might still be needed to allow for certain requirements. for example, shoud Apple be returned or ignored in Pineapple?

 

[Dermot]

I've written a macro to help you. It will return nothing if there is no match

Press Alt-Fll to open the macro editor, then from the menu, Insert, Module and paste this function

Function FindMatchFromList(c As Range, lst As Range) As String
  Static L As Variant, add As String 'make it more efficient by remembering the lookup list if it hasn't changed
  Dim t As String, i As Long
  If lst.Address <> add Then
    L = lst 'load search list into an array
    add = lst.Address
  End If
  t = c 'get text to search for
  For i = LBound(L) To UBound(L)
    If InStr(t, L(i, 1)) > 0 Then
      FindMatchFromList = L(i, 1)
      Exit Function
    End If
  Next i
End Function

Use it like this
= FindMatchFromList(CellAddress,LookupListRange)
See example below

Book3
	B	C	D	E
8	20200727 Apple 1111	Apple		Apple
9	20200727 Orange 1111	Orange		Orange
10	20200727 Apple 2222	Apple		Banana
11	20200727 Banana 2222	Banana
12	20200727 Grape 2222
13	20200727 Apple 2222	Apple
Sheet1

Cell Formulas
Range	Formula
C8:C13	C8	=FindMatchFromList(B8,$E$8:$E$10)

 

[Peter_SSs]

@Gaveon
1. What Excel version are you using? I suggest that you update your Account details (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’)

2. What do you want to happen if the document name was "20322 Orange & Apple 6666"? It looks like your current formula would just return Apple. Is that what you want?