Excel Fox
12-30-2012, 11:55 AM
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
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)),"")
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)),"")