# If string from range is found then VLOOKUP

#### DanniiMarie

##### New Member
I want to search a cell for a string in a range. If one of the strings within the range is found, I want to use a vlookup to return a value from a grid. How would I do this with VBA?

Here is an example of what I want to do (I apologize in advance for the wonky formatting on the bottom grid):

VLOOKUP DATA:
 If this string is found Use this Value Decals 1 Window POP 2 Planogram 3 Counter Card 4 Billboard 5 DL 1 WIN 2 WP 2 PLAN 3 CC 4 BB 5

<tbody>
</tbody>

DATA TO SEARCH:
Cells to SearchReturned Value
BB: 25% Off - code: 25OFF (qty: 3500)5
Counter Card: Take One4
Decals: \$12-15 DEAL OF THE DAY1
 Decals: \$17/18/19 DEAL OF THE DAY

<tbody>
</tbody>
1
 Planogram

<tbody>
</tbody>
3
BB: 25% Off - code: 25OFF (qty: 7000)5
 WP: AMAZING DEAL \$11

<tbody>
</tbody>
2

<tbody>
</tbody>

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi,

Change/adjust Cell references/range as needed, formula copied down.

Change "No Match" to "" (Blank) or whatever you want:

Book1
ABCDEF
1If this string is foundUse this ValueCells to SearchReturned Value
2Decals1BB: 25% Off - code: 25OFF (qty: 3500)5
3Window POP2Counter Card: Take One4
4Planogram3Decals: \$12-15 DEAL OF THE DAY1
5Counter Card4Decals: \$17/18/19 DEAL OF THE DAY1
6Billboard5Planogram3
7DL1BB: 25% Off - code: 25OFF (qty: 7000)5
8WIN2WP: AMAZING DEAL \$112
9WP2ABC SomethingNo Match
10PLAN3
11CC4
12BB5
Sheet536
Cell Formulas
RangeFormula
F2=IFERROR(LOOKUP(2,1/SEARCH(A\$2:A\$12,E2),B\$2:B\$12),"No Match")

Hi,

Change/adjust Cell references/range as needed, formula copied down.

Change "No Match" to "" (Blank) or whatever you want:

ABCDEF
1If this string is foundUse this ValueCells to SearchReturned Value
2Decals1BB: 25% Off - code: 25OFF (qty: 3500)5
3Window POP2Counter Card: Take One4
4Planogram3Decals: \$12-15 DEAL OF THE DAY1
5Counter Card4Decals: \$17/18/19 DEAL OF THE DAY1
6Billboard5Planogram3
7DL1BB: 25% Off - code: 25OFF (qty: 7000)5
8WIN2WP: AMAZING DEAL \$112
9WP2ABC SomethingNo Match
10PLAN3
11CC4
12BB5

</tbody>
Sheet536

Worksheet Formulas
CellFormula
F2=IFERROR(LOOKUP(2,1/SEARCH(A\$2:A\$12,E2),B\$2:B\$12),"No Match")

</tbody>

<tbody>
</tbody>

I was trying to wrap this into a VBA code, but this formula worked in a pinch. Thanks!!!

You're welcome.

Replies
2
Views
223
Replies
8
Views
198
Replies
5
Views
115
Replies
4
Views
175
Replies
0
Views
116

1,206,752
Messages
6,074,730
Members
446,082
Latest member
fgiron83

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