Vlookup from erratically placed contents of a cell?

ChetShannon

Board Regular
Joined
Jul 27, 2007
Messages
126
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,169
Office Version
  1. 365
Platform
  1. Windows
Here is a VBA solution for you

VBA Code:
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

Board Regular
Joined
Jul 27, 2007
Messages
126
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,169
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 27, 2007
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Good idea. I will do that. I have Microsoft 365 aka Excel 365. Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,873
Messages
5,627,398
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top