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.?

Answer 1

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.

Answer 2

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&"'")