If Cell has text in a list, return cell address of LIST match.

[Nemo74]

Hello all,

I've been all over the internet trying to figure this out and I could be making this way harder that it is at this point.

On Sheet 1, I have cell in A1 with a long string " Am grow NHQ Dep: American Growth Type: Liquid State CO : Am Grow".

On Sheet 2 in column A of words starting in A1:

American Growth Type: Solid State
American Growth Type: Liquid State
American Growth Hard Packed: Soil

On Sheet 2 in Column B starting in B1 I have another list of words that are shortened versions of the column A words.

Am Grow SS
Am Grow LS
AM GrowHP S

Now on Sheet 1 B1 what formula can I place in there to return the correct string from Sheet 2, column B?

The correct answer I'm looking to populate in Sheet 1, B2 is AM Grow LS

I'll take a forumla, array, VBA anything!

Thanks peoples!

 

[Nemo74]

This must be a hard one, or something that is not possible. I've never not received at least one shot at a problem . I still trying!

 

[Nemo74]

bump?

Can someone at least tell me its not possible.

 

[Fluff]

How about

Sub FindAbbr()
   Dim ary As Variant
   Dim i As Long
   Dim MyTxt As String
   MyTxt = Sheets("Sheet1").Range("A1")
   ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value
   For i = 1 To UBound(ary)
      If InStr(1, MyTxt, ary(i, 1), vbTextCompare) > 0 Then
         Sheets("Sheet1").Range("B1").Value = ary(i, 2)
         Exit For
      End If
   Next i
End Sub

 

[Nemo74]

AH yes! this does work... just almost too well lol. What if I wanted to expand this to cover say 100 cells in sheet 1, column A and return the answer for each look up? I was thinking of writing a loop and have it change the "Mytext" string as it moves along, but I don't know if that would be the most efficient way of doing it.

Thanks for your help with this Fluff!

 

[Fluff]

For 100 rows, this should be ok

Sub FindAbbr()
   Dim ary As Variant
   Dim i As Long
   Dim Cl As Range
   
   With Sheets("Sheet1")
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value
         For i = 1 To UBound(ary)
            If InStr(1, Cl.Value, ary(i, 1), vbTextCompare) > 0 Then
               Cl.Offset(, 1).Value = ary(i, 2)
               Exit For
            End If
         Next i
      Next Cl
   End With
End Sub

 

[Nemo74]

This works perfect! Thank you very much... I'm kind of glad this was not able to be done with formula's. I was banging my head trying to make a formula work, and while I can edit VBA... I really am bad at creating it.

Thanks again!

[SOLVED]

 

[Aladin Akyurek]

In B1 of Sheet1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&Sheet2!$A$1:$A$3&" "," "&$A1&" "),Sheet2!$B$1:$B$3),"")