Returning multiple results from a lookup

Question

Returning multiple results from a lookup

I'm trying to result multiple results to a single lookup. Right now, if I want to lookup data from a column, it's fairly easy using VLOOKUP as long as all the data in the column is unique. If any of the data repeats, it only returns the first result. I'm looking to have it return all the results. Here's an example:

In the above example, the formula at the top would only return the first result (i.e. for red it would return one, for blue it would return four, etc.). Instead I'd like it to return what is shown in the List table, where all the results that match the lookup are returned. I saw one old post that seemed to say it could do this, but I didn't really understand what he was suggesting, and I'm hoping many years later there is a simpler solution, or if not, that someone could explain how to do it a bit better. Thank you.

Posted on Oct 26, 2021 12:56 AM

Reply

Answer 1

Badunit

Level 7

30,708 points

Oct 26, 2021 10:12 AM in response to Philip Trauring

It would be useful if there was a function that would return all the matches and you could index through them, but there is not.

Shown below is a method adapted from something developed for another user. Maybe it will help. It is not very useful for lookups of many different words but it will do a bunch.

Table 1 is the data

Table 2 is an intermediate table to collect all the matches

Your "List" table would do XLOOKUP on Table 2, searching for the words in row 1 and returning the results from the last row.

Table 2::A2 =XMATCH(B$1,OFFSET(Table 1::$A,A1,0,ROWS(Table 1::$A)−A1,1),0,1)+A1

Table 2::B2 =B1&IFERROR(", "&INDEX(Table 1::$B,A2),"")

Fill down to complete the columns, other than the last row

Table 2::B11 (last row) =TEXTAFTER(B10,", ")

Copy those two columns and paste into the next pair of columns, etc.

Insert new columns to do additional matches

You can transpose the entire table if you like it better with the search words in the first column vs first row. Hopefully it would all transpose correctly.

Your "List" table will use XLOOKUP on Table 2, searching in the first row for the word and the last row for the results. I did not include that part.

One caveat: If a search term contains ", " the formula in row 11 will fail. Better formulas would fix that problem but they start looking messy and are harder to grasp if you are trying to figure out what they do.

Reply