Excel XLS vlookup return multiple values in same cell based on multiple corresponding lookups

Question

I use vlookup often to return a single value based on a single lookup. But I have a case where I have multiple values in a cell separated by commas, and need to return a lookup of corresponding values, also comma separated.

My lookup cells would look like this:

Color	Key
Red	1
Blue	2
Yellow	3
Green	4

Here is what I need in the output (the Keys (joined) column)

Color(s)	Keys (joined)
Red, Yellow	1, 3
Blue	2
Blue, Green	2, 4
Yellow, Blue, Red	3,2,1

I have found a few online references that got close, but not exactly what I need:

https://www.extendoffice.com/documents/excel/2706-excel-vlookup-return-multiple-values-in-one-cell.html

https://trumpexcel.com/multiple-lookup-values-single-cell-excel/

Thank you!

Answer 1

I believe I have exactly what you need. Assuming your Color and Key table starts in A1 and "Red, Yellow" is in cell A8, try this formula in your Keys (joined) column: =join(", ",arrayformula(vlookup(SPLIT(A8, ", "),$A$2:$B$5,2,FALSE)))

This formula splits colors from a cell, does an individual vlookup for each color, then joins them together into an output for you.