Years back when I wrote my Excel book, I had to create an index for the book. I shared the file I used including the macro in this post. Recently I thought dynamic arrays could do much of the work for this.

The layout of the table uses the three columns on the left for the levels of the topics within the Index. The rest of the columns have each page number in the book. You place an x in a column if the topic is covered on that page. You can download the example file using the button at the bottom of the post.

We need an easy way to combine each topic’s page numbers separated by a comma on each row. That is what the macro did in the original file macro. In the latest version of Excel dynamic array formulas can do the same in a single formula.

The formula is surprisingly short for what it does.

The formula in QF2 is.

=TEXTJOIN(",",1,IF(D2:QD2="x",$D$1:$QD$1,""))

The TEXTJOIN function joins text together from entries in a range.

The first argument is the separator character to place between the entries – in this case a comma.

The second argument is whether to ignore blanks. You can use TRUE or 1 to ignore blanks.

The third argument is the range to use. We have used an IF function to create the range. This requires the subscription version of Excel or Excel 2021 or later to work.

Because we have referred to two horizontal ranges D2:QD2 and $D$1:$QD$1 the IF function will go through each cell in each range and create another range based on the TRUE and FALSE results.

If the cell on row 2 has an x, then the corresponding number in the same column in row 1 is returned. If the cell on row 2 doesn’t have an x, a blank is returned. The IF function will create a range with numbers and blanks.

Because the TEXTJOIN function we created ignores blanks, it only returns the numbers separated by a comma.

This example shows some of the flexibility of dynamic arrays.

Download Example File