Vlookup from erratically placed contents of a cell?

[ChetShannon]

I am trying to find a way to do a lookup based on the text contents of a cell and the target text is not regularly placed in the cell. For example the cell might have the word RED or BLUE or GREEN placed in any particular position in the cell and I want to have another cell (calling it the sister cell) return a value from a lookup table (in the same workbook) based on the first cell having RED, BLUE or GREEN in it. For example if the cell has RED in it then the sister cell would do a lookup and return 10.0 if it's RED, or 20 BLUE is in the cell or 30 if GREEN is in the cell somewhere.

A1 might be "THE SKY IS RED" and B1 would return 10 because A1 has RED somewhere in it. The letters RED could be anywhere in A1 though so using MID might not work since you don't where the text of RED will be. The next time A1 might be "BLUE IS THE SKY" and B1 would return 20 because BLUE was somewhere in A1.

I hope I am explaining this well enough. Thanks! Chet

 

[alansidman]

Here is a VBA solution for you

Option Explicit
Option Compare Text


Sub Colors()
    Dim i As Long, lr As Long
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        If InStr(Range("A" & i), "RED") > 0 Then
            Range("B" & i) = 10
        ElseIf InStr(Range("A" & i), "BLUE") > 0 Then
            Range("B" & i) = 20
        ElseIf InStr(Range("A" & i), "GREEN") > 0 Then
            Range("B" & i) = 30
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "completed"
End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[ChetShannon]

Thank you so much for your response!... Unfortunately I was trying to do this just as a formula in a cell. Anyone know how to do this as a cell formula?).. thx!

 

[alansidman]

Suggest you amend your profile to indicate which version of excel you are using as this may influence the solution. Some features in the newer versions are not applicable in older versions.

 

[ChetShannon]

Good idea. I will do that. I have Microsoft 365 aka Excel 365. Thanks!