Lookup multiple values in a single cell (comma separated) and then return the values to a single cell (also comma separated)

Question

Using Google Sheets, I have the following two tables:

1st table

Size   | Code
--------------
Large  | L
Small  | S
XLarge | XL

2nd table

Values       | Codes
-------------------
Large,Small  | L,S
XLarge,Small | XL,S
XLarge,large | XL,L

I need a lookup function on Codes column to return Codes for the according values.

When I used:

=LOOKUP(Values Column,Size,Code)

I got only one code for example L. How can I get codes: (L,S) (XL,S) (XL,L) etc.?

Please don't force tags into the the title. See es.stackoverflow.com/help/tagging. Also if the question is exclusively about [tag:google-spreadsheet] don't include [tag:excel] because while both are spreadsheets application they have several things that doesn't work in the same way. — Rubén, Apr 14 '17 at 22:24

score 4 · Accepted Answer · answered Apr 14 '17 at 20:35

You have to split them, do a vlookup, then concatenate the results

=arrayformula(left(concatenate(vlookup(split(D2,","),$A$2:$B$4,2,false)&","),len(concatenate(vlookup(split(D2,","),$A$2:$B$4,2,false)&","))-1))

Where my lookup table is in A2:B4 and the Values start in D2.

score 0 · Answer 2 · answered Apr 14 '17 at 18:55

0

You could try query. If table 1 is in Sheet1 and table 2 is in Sheet2, in Sheet3 enter the size to find in A2 (i.e.'S') and enter this query in B2:

 =Query({Sheet1!A2:B4,Sheet2!A2:B4},"Select Col4 where Col4 contains '"&A2&"'")

answered Apr 14 '17 at 18:55

Ed Nelson

2 Answers2