Reverse Wildcard Vlookup

[Alohadboy]

Hello - this site was a huge help in the last problem I was faced with so I thought I would try again.

I have 2 columns of data as such, one that contains various site names ("Site") and the other to contain a Code. I want to populate the "Code" column using the Legend below:
Site_____________Code
Seattle Trans
Idaho Plant
Nevada Trans
Portland Admin

I have a "Code" legend set up as such:
Trans 100
Plant 300
Admin 500
etc...

I need a formula that I can enter into the "Code" column above that will return the value from the legend. Ex, any cell that contains the word "trans" would populate the column with "100", etc. Obviously there is a massive list and a more exhaustive legend so a formula is the answer.

I have tried both of the following formulas with no luck:
=VLOOKUP("*"&B5&"*", $J$5:$K$12, 2, 0) = where B5 is the "site" above and $J$5:$K$12 is the "legend".
=VLOOKUP(B5, "*"&$J$5:$K$12&"*", 2, 0)

Any help is appreciated. Thanks.

 

[njimack]

To retrieve the text after the space, which you can then build into your VLOOKUP function, use:
RIGHT(B5,LEN(B5)-FIND(" ",B5))

 

[T. Valko]

Hello - this site was a huge help in the last problem I was faced with so I thought I would try again.

I have 2 columns of data as such, one that contains various site names ("Site") and the other to contain a Code. I want to populate the "Code" column using the Legend below:
Site_____________Code
Seattle Trans
Idaho Plant
Nevada Trans
Portland Admin

I have a "Code" legend set up as such:
Trans 100
Plant 300
Admin 500
etc...

I need a formula that I can enter into the "Code" column above that will return the value from the legend. Ex, any cell that contains the word "trans" would populate the column with "100", etc. Obviously there is a massive list and a more exhaustive legend so a formula is the answer.

I have tried both of the following formulas with no luck:
=VLOOKUP("*"&B5&"*", $J$5:$K$12, 2, 0) = where B5 is the "site" above and $J$5:$K$12 is the "legend".
=VLOOKUP(B5, "*"&$J$5:$K$12&"*", 2, 0)

Any help is appreciated. Thanks.

One way...

Book1
	A	B	C	D	E
2	Seattle Trans	100	_	Trans	100
3	Idaho Plant	300	_	Plant	300
4	Nevada Trans	100	_	Admin	500
5	Portland Admin	500	_	_	_
Sheet1

Formula entered in B2 and copied down:

=LOOKUP(1E100,SEARCH(D$2:D$4,A2),E$2:E$4)

 

[Alohadboy]

One way...

Sheet1

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:106px;"><col style="width:72px;"><col style="width:29px;"><col style="width:72px;"><col style="width:72px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">Seattle Trans</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">100</td><td style="color:#ffffff; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Trans</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">100</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Idaho Plant</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">300</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Plant</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">300</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Nevada Trans</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">100</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Admin</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">500</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Portland Admin</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">500</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></tbody></table>


Formula entered in B2 and copied down:

=LOOKUP(1E100,SEARCH(D$2:D$4,A2),E$2:E$4)

Valko, this worked. Thank you. Can you shed some light on what the "1E100" is? Thanks.

 

[T. Valko]

Valko, this worked. Thank you. Can you shed some light on what the "1E100" is? Thanks.

1E100 is scientific notation for a very large number, 1 followed by 100 zeros.

The SEARCH function will return either an error (no match was found) or a number from 1 to 32,767.

For the formula to work correctly we need a lookup value, 1E100, that is greater than or equal to 32,767.

I use 1E100 because it's greater than or equal to 32,767 and it's easy to remember.

 

[Alohadboy]

Valko, thanks for the help. I have a similar question (don't know if I need to start another thread or not) and couldn't figure it out.

Using the same format with 2 columns of data as such, one that contains various site names ("Site") and the other to contain a State ID #. I want to populate the "ID" column using the Legend below:
Site_____________ID
Seattle Trans
Houston Plant
Nevada Trans
Portland Admin

I have a "ID" legend set up as such:
State______ID___City 1___City 2___City 3 etc.
Washington _02__ Seattle__ Aberdeen__ Blaine__ etc
Texas ______03 __El Paso__ Dallas___ Houston__ etc
etc...

As I populate the legend with different cities out to the right I want the corresponding legend "ID" to populate in the "ID" field next to the "Site" column in the top chart. Ex, Seattle Trans would have an "02" in the ID column and Houston Plant would have an "03" (as would anything else out to the right of "Texas").

I tried to modify the formula you provided but I guess it only works if your lookup info is in one column or row.

Thanks again.

 

[T. Valko]

Valko, thanks for the help. I have a similar question (don't know if I need to start another thread or not) and couldn't figure it out.

Using the same format with 2 columns of data as such, one that contains various site names ("Site") and the other to contain a State ID #. I want to populate the "ID" column using the Legend below:
Site_____________ID
Seattle Trans
Houston Plant
Nevada Trans
Portland Admin

I have a "ID" legend set up as such:
State______ID___City 1___City 2___City 3 etc.
Washington _02__ Seattle__ Aberdeen__ Blaine__ etc
Texas ______03 __El Paso__ Dallas___ Houston__ etc
etc...

As I populate the legend with different cities out to the right I want the corresponding legend "ID" to populate in the "ID" field next to the "Site" column in the top chart. Ex, Seattle Trans would have an "02" in the ID column and Houston Plant would have an "03" (as would anything else out to the right of "Texas").

I tried to modify the formula you provided but I guess it only works if your lookup info is in one column or row.

Thanks again.

I think I would extract the city name to its own cell like this:

Book1
	A	B	C
2	Seattle Trans	Seattle	2
3	Houston Plant	Houston	3
4	Nevada Trans	Nevada	_
5	Portland Admin	Portland	4
6	McKees Rocks Admin	McKees Rocks	6
7	Sault Ste. Marie Plant	Sault Ste. Marie	5
Sheet2

Those 2 and 3 word city names can cause problems!

Book1
	A	B	C	D	E
10	State	ID	City 1	City 2	City 3
11	Washington	2	Seattle	Aberdeen	Blaine
12	Texas	3	El Paso	Dallas	Houston
13	Maine	4	Portland	_	_
14	Michigan	5	Detroit	Sault Ste. Marie	_
15	Pennsylvania	6	Pittsbugh	McKees Rocks	_
Sheet2

This formula entered in B2 and copied down to extract the city name:

=LEFT(A2,FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)

This array formula** entered in C2 and copied down to return the ID:

=IF(COUNTIF(C$11:E$15,B2),INDEX(B$11:B$15,MAX(IF(C$11:E$15=B2,ROW(C$11:E$15)-ROW(C$11)+1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[seenai]

Hi, I found this formula is very useful. =LOOKUP(1E100,SEARCH(D$2:D$4,A2),E$2:E$4)
This formula result is searching text, if available anywhere in A2. But I need the result, only if it is beginning of text in A2 cell. Help me for modification.