# Vlookup from erratically placed contents of a cell?

#### ChetShannon

##### Board Regular
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### alansidman

##### Well-known Member
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
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
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
Good idea. I will do that. I have Microsoft 365 aka Excel 365. Thanks!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,527
Messages
5,854,269
Members
431,630
Latest member
Aleksandru

### 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.

### Which adblocker are you using?

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

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