Combine Search and Vlookup?

Gaveon

New Member
Joined
Aug 11, 2016
Messages
4
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,
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,056
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
BCDE
820200727 Apple 1111AppleApple
920200727 Orange 1111OrangeOrange
1020200727 Apple 2222AppleBanana
1120200727 Banana 2222Banana
1220200727 Grape 2222 
1320200727 Apple 2222Apple
Sheet1
Cell Formulas
RangeFormula
C8:C13C8=FindMatchFromList(B8,$E$8:$E$10)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,919
Office Version
  1. 365
Platform
  1. Windows
@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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,655
Messages
5,549,248
Members
410,905
Latest member
Extjel
Top