If string from range is found then VLOOKUP

DanniiMarie

New Member
Joined
May 21, 2018
Messages
30
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 foundUse this Value
Decals1
Window POP2
Planogram3
Counter Card4
Billboard5
DL1
WIN2
WP2
PLAN3
CC4
BB5

<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")
 
Upvote 0
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet536

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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

Forum statistics

Threads
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.
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
Back
Top