Use Native Excel Function Lookup Formula In VBA

[Rick Rothstein]

There is a range of cells contains the following words (F4 to F19)

Bridal
Bars
Body Polishing
Clinics
Android
Comics
Lighting
Android
Crystals
Grocery
Waxing
Labs
Flip Phones
Drama
Timer
Flip Phones

There is a sentance 'Aluminium Yoga Bridal Dealers Waxing' in A2

If i try it out in Excel function, i can get the data through the following Excel function
=LOOKUP(2^15,SEARCH(F4:F19,A2),F4:F19)

Can you clarify something for me? The sentence that I highlighted in red contains two words from your list (Bridal and Waxing), but your formula only returns the last one (Waxing); however, if you remove that word (Waxing) from the sentence, then your formula will return first word (Bridal) as it is now the only word in the sentence. Is this the functionality you really wanted? Wouldn't you really want all words from the list that appear in the sentence returned, perhaps as a comma delimited list?