Friday, June 13, 2014

yogi_Use VLOOKUP Function For A String Of Comma Separated Values

                                         Google Spreadsheet   Post  #1673
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jun-13-2014
post by sonouna: (!mydiscussions/docs/x-gkF7KxeRE)
VLookup with CSV in input cell?
I need to perform a VLookup function that has comma separated values as input, the output ideally would also be comma separated.
Here is sample data:

Range List
TKA   TKAimg.jpg
TKF   TKFimg.jpg
TKWO  TKWOimg.jpg
TKMO  TKMOimg.jpg

For input:

I need output:
TKAimg.jpg, TKFimg.jpg

=VLookup(input, range, index, FALSE) works fine with a single input value, I found some forum posts about a MultiVLookup function but Google Spreadhseet gives me a #NAME? error "Unknown function".

Any idea how to solve this?

I thought I could have a few intermediate columns that split the input column into single values and then concatenate the outputs... but that adds a lot of "no-touchy" parts to my document, and rises more questions (e.g. how many values max will people concatenate?)


Thanks Yogi!
Here is a working spreadhseet

My reference values are in table A1:B7, License and Media.

In table E1:F4 I have tried simple VLookup with the following formula:

and with single "License" type input (such as TK F or TK CO) in column E I do get Media output in column F. This one worked just fine.

Table E6:F8 shows what I am trying to accomplish, I expect users to add comma separated License type input, the kind you see in E7 or E8. For these, I would expect in F7 and F8 the following output:

E7 = TK F, TK A
F7 = lc_label_family.pnglc_label_attribution.png

F8 = lc_label_communityuse.pnglc_label_men.png

I tried the following formula:
but as I said I get an error, so I'd love to be able to accomplish this just with VLookup like you say.
Thanks a lot!