CODEWITHSUNDEEP
excel-formulagoogle-sheets-formulavlookup
Blake Miller
Blake Miller

Reputation: 1

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

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!

Upvotes: 0

Views: 746

Answers (1)

Mathx2
Mathx2

Reputation: 37

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.

Upvotes: 0

Related Questions