VLookup Any Matching Word From A Sentence

Many a times, we've come across a situation where we don't want to look-up the entire content of a cell, but only a word / few words within the entire text, against a column of words. One way of doing it is through a UDF like this one

Code:

---

Function WLOOKUP(strText As String, rng As Range) As String

    Dim lng As Long, lngI As Long
    On Error Resume Next
    For lng = LBound(Split(strText, " ")) To UBound(Split(strText, " "))
        lngI = Application.Match(Split(strText, " ")(lng), rng, 0)
        If lngI <> 0 Then
            WLOOKUP = rng.Cells(lngI)
            Exit Function
        End If
    Next lng
   
End Function

---

If you don't want VBA, and want to stick to native formulas, here's a way to do it.

Create 2 named ranges, 1. WORDS which contains a column of words that you want to look-up from
2. WLKUP =LEN(SUBSTITUTE(" "&Sheet1!A2&" ",WORDS,"")) where Sheet1!A2 and below are a list of sentences from which we want to look up any available word of words in a given sequence...

And use the formula =IF(FREQUENCY(WLKUP,MIN(WLKUP))=1,INDEX(WORDS,MATC H(MIN(WLKUP),WLKUP,0)),"")