vlookup with comma separated values

[drewmiller]

Im wanting to do a vlookup in a column that has multiple comma separated values. Basically something like this:

Table 1
Lookup: "75205"

-in-

Table 2
Cell A1: "75203, 75204, 75205, 75206, 75207"

Return corresponding value:
Cell B1: "Dallas"

Thanks in advance!

 

[eibi]

Well -- the array formula below might work...

=INDEX(B1:B2,MAX((LEN(A1:A2)<>LEN(SUBSTITUTE(A1:A2,B4,)))*(ROW(A1:A2))))

It would help to have a larger data sample in order to run some checks...

See attached.

 

[Nebu]

Hi:

Non-CSE formula
=LOOKUP(2^15,SEARCH(B4,A1:A2),B1:B2), formula in colored cell.

Thanks

 

[bosco_yip]

Another possible non-CSE formula:

=INDEX(B1:B2,MATCH("*"&B4&"*",A1:A2,0))

Edit : Difference between this formula and the above 2 formulas is :

1. This [3] formula search from top to down and return the first match.

2. The above [1] and [2] formulas search from bottom to up and return the last match.

3. If the Lookup range value is unique, the 3 formulas result will return in same.

4. If the Lookup range value have duplicate, [1] and [2] formulas will give same result, while [3] formula result maybe different.


Regards
Bosco

 

[drewmiller]

Looks like I spoke too soon, it seems like the code is not working for zipcodes with a larger set of numbers.

See sample.

 

[eibi]

Note that in the sample file you provided, you need to update the range in the original formula that bosco_yip provided:

=INDEX($B$2:$B$4,MATCH("*"&D4&"*",$A$2:$A$4,0))

Then -- it works as long as the text string in the cell is less than 256 characters (see attached).

There may be a 256 character limit on the functionality of the lookup() and match() functions?

 

[p45cal]

Yes, there were 3 reasons the formula =INDEX($B$2:$B$3,MATCH("*"&D4&"*",$A$2:$A$3,0)) did not work, all of them cited by eibi.
To beat the 255 character limit try, in cell E2 array-entering (that's entering with Ctrl+Shift+Enter instead of just Enter) the formula:

=INDEX($B$2:$B$4,MATCH(TRUE,SUBSTITUTE($A$2:$A$4,D2,"")<>$A$2:$A$4,0))

and copy down.

 

[blazy]

Pascal, could you quickly explain this solution?

Thanks

 

[p45cal]

Quickly.. ha!

Set up a sheet like this:

but in D2 have array-entered:

=INDEX($B$2:$B$4,MATCH(TRUE,SUBSTITUTE($A$2:$A$4,C2,"")<>$A$2:$A$4,0))

With D2 selected, go to the Formulas tab of the ribbon and in the Formula Auditing section choose Evaluate Formula.
After two clicks of the Evaluate button you should see:

where I've highlighted in yellow what's highlighted in green will become (because you won't see it later).
A further click gets you:

where I've circled in red you can see the missing E. These 3 strings are going to be compared to what's highlighted in yellow in the earlier picture yielding this at the next Evaluate button click:

where TRUE is going to be matched against False;True;False yielding 2:

etc.

The only other thing to note is when there are more than 255 characters in a cell, in the Evaluate Formula dialogue box you'll see #Value!, but behind that things are being properly evaluated: (picture in next message).

 

[p45cal]

final picture:

 

[Nebu]

Hi:

Does this formula give the same results?

=LOOKUP(2^15,SEARCH(D2,$A$2:$A$4),$B$2:$B$4)

Or am I missing something?

Thanks

 

[p45cal]

give the same results? … Or am I missing something

The latter.

 

[blazy]

Hi Pascal,

thanks!

What about if there were text "CBA" or "CB",... So if order is revesed or random, (if we woulf have more letters)?

Thanks,

 

[p45cal]

Hi Pascal,

thanks!

What about if there were text "CBA" or "CB",... So if order is revesed or random, (if we woulf have more letters)?

Thanks,

It should work… try it!

 

[blazy]

Tried it, doesn't work for me.

Also doensn't work if I put lower case letters.

Do you have solution for these 2 scenarios?

 

[p45cal]

What about if there were text "CBA" or "CB",... So if order is revesed or random, (if we woulf have more letters)?

Don't know where these would be.
Supply a file.