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

	A​	B​
1​	Location​	ZIPs ​
2​	Los Angeles​	900,901,902,908​
3​	Industry PDC​	918,917,906​
4​	Santa Ana PDC​	926,927​

<tbody>
</tbody>

Main sheet

	A​	B​
1​	ZIP​	Location​
2​	900​	Los Angeles​
3​	918​	Industry PDC​
4​	926​	Santa Ana PDC​
5​

<tbody>
</tbody>

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.