If string from range is found then VLOOKUP

[DanniiMarie]

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:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]If this string is found[/TD]
[TD]Use this Value[/TD]
[/TR]
[TR]
[TD]Decals[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Window POP[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Planogram[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Counter Card[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Billboard[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]DL[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]WIN[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]WP[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]PLAN[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]




















DATA TO SEARCH:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Cells to Search[/TD]
[TD="align: center"]Returned Value[/TD]
[/TR]
[TR]
[TD]BB: 25% Off - code: 25OFF (qty: 3500)[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD]Counter Card: Take One[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]Decals: $12-15 DEAL OF THE DAY[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 310"]
<tbody>[TR="class: grid"]
[TD="width: 310"]Decals: $17/18/19 DEAL OF THE DAY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 310"]
<tbody>[TR="class: grid"]
[TD="width: 310"]Planogram[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]BB: 25% Off - code: 25OFF (qty: 7000)[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD][TABLE="width: 310"]
<tbody>[TR="class: grid"]
[TD="width: 310"]WP: AMAZING DEAL $11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

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

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

Book1
	A	B	C	D	E	F
1	If this string is found	Use this Value			Cells to Search	Returned Value
2	Decals	1			BB: 25% Off - code: 25OFF (qty: 3500)	5
3	Window POP	2			Counter Card: Take One	4
4	Planogram	3			Decals: $12-15 DEAL OF THE DAY	1
5	Counter Card	4			Decals: $17/18/19 DEAL OF THE DAY	1
6	Billboard	5			Planogram	3
7	DL	1			BB: 25% Off - code: 25OFF (qty: 7000)	5
8	WIN	2			WP: AMAZING DEAL $11	2
9	WP	2			ABC Something	No Match
10	PLAN	3
11	CC	4
12	BB	5
Sheet536

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

 

[DanniiMarie]

Hi,

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

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

	A	B	C	D	E	F
If this string is found	Use this Value	Cells to Search	Returned Value
Decals	BB: 25% Off - code: 25OFF (qty: 3500)
Window POP	Counter Card: Take One
Planogram	Decals: $12-15 DEAL OF THE DAY
Counter Card	Decals: $17/18/19 DEAL OF THE DAY
Billboard	Planogram
DL	BB: 25% Off - code: 25OFF (qty: 7000)
WIN	WP: AMAZING DEAL $11
WP	ABC Something	No Match
PLAN
CC
BB

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet536​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IFERROR(LOOKUP(2,1/SEARCH(A$2:A$12,E2),B$2:B$12),"No Match")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[jtakw]

You're welcome.