is it possible to use Index Match and return multiple results (Nth) all in ONE cell?

[jonagpa]

Good morning!

Wondering if we can take the index match Nth formula but instead of having each Nth result in its own cell, we can concatenate all results into one cell.
index match Nth formula example: https://exceljet.net/formula/get-nth...th-index-match

So here is how I currently use the index match formula:

INDEX(ResultRange,
SMALL(
IF(
(CriteriaRange1=Criteria1)*
(CriteriaRange2=Criteria2),
ROW(RowRange1)-ROW(INDEX(RowRange1,1,1))+1),
ROWS(B$2:B2)

I have to be honest, not sure how the last section really works (after ROW), I just know that it returns the row number of the cell that matches the criteria and ROWS tells the formula which Nth result it should return depending on the current row number.

All I want to do now is concatenate each result into one cell. So I want to have the 1st_result & 2nd_result & 3rd_result. This means I cant use the current row to determine which Nth result it should show.
the first issue i see, is that since we can no longer use row, we will need to repeat the use of index match for every Nth result we expect, and the number of Nth results will be limited by the number of times we use index match. In my case, thats ok since I expect no more than say 20 results, although I am worried about the amount of resources this will use.
I am guessing that it will involve the COUNTIFS function to count how many results there are and the IF function could be use to avoid running each index match function, only running it if the number of results is equal or less than the Nth result were asking for.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.