Exceljet

Quick, clean, and to the point

Extract multiple matches into separate columns

Excel formula: Extract multiple matches into separate columns
Explanation 

To extract multiple matches to separate cells, in separate columns, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in F5 is:

{=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),"")}

This is an array formula and must be entered with Control + Shift + Enter.

After you enter the formula in the first cell, drag it down and across to fill in the other cells.

How this formula works

Note: this formula uses two named ranges: "names" refers to C5:C11, and "groups" refers to B5:B11. These names are defined in the screen shot above as well.

The gist of this formula is this: we are using the SMALL function to generate a row number corresponding to an "nth match". Once we have the row number, we simply pass it into the INDEX function function, which returns the value at that row.

The trick is that SMALL is working with an array that is dynamically constructed by IF in this bit:

IF(groups=$E5,ROW(names)-MIN(ROW(names))+1)

This snippet tests the named range "groups" for the value in E5. If found, it returns a row number from an array of relative row numbers created with:

ROW(names)-MIN(ROW(names))+1

The final result is an array that contains numbers where there is a match, and FALSE where not:

{1;FALSE;FALSE;FALSE;FALSE;6;FALSE}

This array goes into SMALL. The k value for SMALL (nth) comes from an expanding range:

COLUMNS($E$5:E5)

When copied across the result table, the range expands, causing k (nth) to increment. The SMALL function returns each matching row number, which is supplied to the INDEX function as row_num, with the named range "names" as the array.

Handling errors

When COLUMNS returns a value for k that does not exist, SMALL throws a #NUM error. This happens after all matches have occurred. To suppress the error, we wrap the formula in the IFERROR function to catch errors and return an empty string ("").

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.