VLOOKUP from Partial Strings

[Senior_OIE]

I have a report that I need to get the return as the facility name. I am trying to see if a formula or a macro will solve my problem.
So in the sample below, I need to look up values on the First Sheet Column A (3 digit zips) and compare it with column B (3 digit zip to look up from) on the Second Sheet and return values on Column A (Facility Name) and place it on First Sheet Column B. Now if you notice Column B on Second sheet has a sting of values which represent the range of zips that belongs to a certain facility. That is the problem in my look up. I am not really familiar with vlookup with partial strings involved.

First Sheet:
Column A (3 digit zip-lookup value) Column B (where to place return value facility name)
900 Los Angeles PDC
918 Industry PDC
926 Santa Ana PDC

Second Sheet
Column A (Facility Name - return value) Column B (3 digit zips - look up from)
Los Angeles ,900,901,902,907,908
Industry PDC ,918,917,906,
Santa Ana PDC ,926,927,

 

[Marcelo Branco]

Maybe something like

B2 copied down
=INDEX(Sheet2!A:A,MATCH("*"&A2&"*",Sheet2!B:B,0))

Hope this helps

M.

 

[Special-K99]

I cant get that to work.
Keeps comin up with #N/A error.

 

[Marcelo Branco]

I cant get that to work.
Keeps comin up with #N/A error.

Worked for me
Sheet2
[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Location​

[/TD]
[TD]

ZIPs
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Los Angeles​

[/TD]
[TD]

900,901,902,908​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Industry PDC​

[/TD]
[TD]

918,917,906​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Santa Ana PDC​

[/TD]
[TD]

926,927​

[/TD]
[/TR]
</tbody>[/TABLE]


Main sheet

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

ZIP​

[/TD]
[TD]

Location​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

900​

[/TD]
[TD]

Los Angeles​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

918​

[/TD]
[TD]

Industry PDC​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

926​

[/TD]
[TD]

Santa Ana PDC​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in B2 copied down
=INDEX(Sheet2!A:A,MATCH("*"&A2&"*",Sheet2!B:B,0))

M.

 

[Senior_OIE]

no this didnt work.

What I needed is First sheet Column B to be my main area to write the formula from. Also there is only one lookup value i use and compare it against a column of strings so for example I want to look up 900 from a series of numbers ,900,901,902,907,908,. since 900 is within that series of strings separated by commas, it will then return Los Angeles PDC and place it in Column B on the first sheet.. I don't know if a formula works for that or a macro will work better.

 

[Marcelo Branco]

no this didnt work.

What I needed is First sheet Column B to be my main area to write the formula from. Also there is only one lookup value i use and compare it against a column of strings so for example I want to look up 900 from a series of numbers ,900,901,902,907,908,. since 900 is within that series of strings separated by commas, it will then return Los Angeles PDC and place it in Column B on the first sheet.. I don't know if a formula works for that or a macro will work better.

Worked perfectly for me. See post 4
Assumes the name of data sheet = Sheet2. Check

M.

 

[Marcelo Branco]

A more robust solution would be an array formula using definite ranges (recommended) like
=INDEX(Sheet2!A$2:A$1000,MATCH("*,"&A2&",*",","&Sheet2!B$2:B$1000&",",0))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Senior_OIE]

oh you are right, it works. i just had issues with my columns as i had other columns before and after my lookup values. had to edit the formula a bit. but thanks!

 

[Marcelo Branco]

oh you are right, it works. i just had issues with my columns as i had other columns before and after my lookup values. had to edit the formula a bit. but thanks!

Yes, it works!
You are welcome. Thanks for the feedback.

M.