Multiple matches into separate rows

In this example, the goal is to get all names in a given group into separate rows grouped by column, as seen in the worksheet above. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns where each column holds the names that belong to a group. The article below explains two options (1) a modern approach based on the FILTER function and (2) a legacy approach based on INDEX and SMALL for older versions of Excel without the FILTER function.

Modern approach

Several new functions in the current version of Excel make this task easier. First, to build a list of groups in alphabetical order we use the UNIQUE function with the SORT function in cell E4 like this

=TRANSPOSE(SORT(UNIQUE(group))) // returns {"A","B","C","D"}

The TRANSPOSE function converts the vertical output from SORT into a horizontal array. This formula spills the four unique group names into the range E4:H4. With the groups in place, we now have what we need to retrieve the names in each group. For this step, we use a formula like this in cell E5:

=FILTER(name,group=E4)

The FILTER function retrieves the names in B5:B16 where the group = "A" (the value in E4). As the formula is copied across, the reference to E5 is relative and changes at each new row. The result is all names in each group are together in the same column.

Note: it would be nice to use a reference to the spill range in E4:H8 (E4#) inside the FILTER function. However, Excel formulas won't currently return an array-of-arrays so this doesn't work.

Legacy solution

In older versions of Excel that don't offer the FILTER function, you can use a more complex array formula based on the INDEX function and the SMALL function to get multiple matches into separate columns. Enter the formula below in cell E5, then drag it down and across to fill in the other cells in the range E5:H7:

=IFERROR(INDEX(name,SMALL(IF(group=E$4,ROW(name)-MIN(ROW(name))+1),ROWS($E$5:E5))),"")

Note: this is an array formula and must be entered with Control + Shift + Enter in older versions of Excel.

The gist of this formula is this: we are using the SMALL function to generate a row number corresponding to an "nth match" for each name in a group. Once we have the row number, we pass it into the INDEX function, which returns the value at that row. To make this work, we need to "pre-filter" the array of values given to SMALL to exclude other groups. We do this with the IF function in this part of the formula:

IF(group=E$4,ROW(name)-MIN(ROW(name))+1)

At a high level, this snippet gets the row numbers for all names that belong to a given group. It does this by testing the group in cell E4 against all values in the named range group. When the result is TRUE, the IF function returns the row number (see next step). When the result is FALSE, the IF function returns FALSE. The row numbers are created with the formula below:

ROW(name)-MIN(ROW(name))+1

See this page for details. The final result is an array like this:

{1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;9;FALSE;FALSE;FALSE}

As you can see, the only row numbers that survive the filtering are those that correspond to the group in cell E4. This array goes into SMALL as the array argument. The value for k is created with an expanding range and the ROWS function:

ROWS($E$5:E5) // value for k

As the formula is copied down the table, the range expands, causing k to increment. The result is that the SMALL function returns the row number for each name in a given group. This number is supplied to the INDEX function as row_num, with the named range name as the array argument, and INDEX returns the name associated with each row number.

Handling errors

When ROWS returns a value for k that does not exist, SMALL throws a #NUM error. This happens after all names for a given group have been extracted. To suppress this error, we wrap the formula in the IFERROR function and return an empty string ("").