Extract multiple matches into separate columns

An excel formula to extract multiple matches into separate columns

 Extract multiple matches into separate columns

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:


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

The gist of this formula is this: we are using the SMALL function to get a row number that corresponds 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:


This snipped tests a value to see if it exists in the range "names". If so, it returns a "normalized" row number from an array of row numbers created by this:


The final result is an array that contains numbers where there is a match, and FALSE where not. The array looks something like this:


This is the array that goes into SMALL. The k value for SMALL (nth) comes from this:


Note that this is an expanding range that will change when copied across the result table. This is what causes k (nth) to increment. 

One problem with this formula is that when COLUMNS returns a value for k that does not exist, it throws a #NUM error. We use IFERROR to catch the error and return "" when needed.


0 votes. 0 / 5