Excel: Lookup multiple values in one cell and return results in another

Question

I'm trying to find a way to lookup each of several comma separated values in one cell, and return the results as a comma separated list in another cell.

The number of values to lookup is not constant, it may be only one, or several hundred.

Example - Sheet A has the initial values and will hold the returned values. Sheet B is the table with the data to lookup.

Answer 1

EDIT: I've gotten better and learned how to make this a 1-liner. I'll leave my original answer below, but here's my updated one (as this hasn't been accepted or even commented on, yet):

=TEXTJOIN(", ",TRUE,INDEX(SheetB!$A$2:$B$6,MATCH(FILTERXML("<x><y>"&SUBSTITUTE($B2,",","</y><y>")&"</y></x>","//y"),SheetB!$A$2:$A$6,0),2),"")

I think INDEX/MATCH was the key component I hadn't learned yet when I tried answering this before. :)

The FILTERXML/SUBSTITUTE I had learned from another site where it takes in your delimited values and replaces the delimiter and wraps the rest with the needed xml tags for FILTERXML to then return an array of your values.

INDEX/MATCH then does your lookup, first getting the row number via MATCH (note the final value of 0 in MATCH means exact match), then INDEX returns the value of the corresponding matching column (the 2 at the end of INDEX's arguments).

Finally, TEXTJOIN joins the results together with the delimiter of your choice (and the second argument set to TRUE allows it to skip blanks).

As a final note, I think this solution may work without Office 365. FILTERXML appears to have been available as early as Office 2013.

---

Original, outdated answer follows:

I've been searching for an answer to this myself and was rather dismayed to find your question - exactly the question I have - without an answer.

Almost 4 years late, this answer does require Office 365 Excel, and it's not the most elegant, but here's what I've been able to come up with.

Pick an unused column on your Sheet B (with enough contiguous unused columns to its right to handle spillover of however many values you'll need to be splitting, or use a new sheet dedicated to this) and put this formula into the 2nd row's cell for that column, dragging it down through each cell that has corresponding data back on Sheet A (and assuming your column header "File #s" is column B): =TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(SheetA!$B2,delim,"</y><y>")&"</y></x>","//y"))

Replace delim with your delimiter wrapped in quotes, or a cell reference to a cell that has your delimiter in it. Also, if it's possible to have no value in the corresponding cell in Sheet A, then you'll need to wrap the FILTERXML() function (or the whole thing) with IFERROR().

Then, back on Sheet A in your Results column, use this formula: =TEXTJOIN(delim,TRUE,FILTER(SheetB!$B$2:$B$6,COUNTIF(SheetB!D2#,SheetB!$A$2:$A$6),if_no_match))

Again, replace delim with the delimiter or cell reference with your delimiter of choice. SheetB!$B$2:$B$6 and SheetB!$A$2:$A$6 are your lookup table's columns (and thus extend them to encompass the whole thing). The SheetB!D2# references the column where you put the TRANSPOSE(FILTERXML()) formula. Finally, replace if_no_match with whatever you want to appear if there was no match.

---

I'd ideally like to find a way that uses a single, self-contained formula, but alas, this is as far as I've managed so far.