Is there a way to drag formula down but changing the column

Question

I'm trying to make a summary which list out all the matches and do a transpose of the table. I wrote a formula which works successfully. But, I come into another problem, which I find my formula cannot be dragged down and apply to all rows. Can anyone assist me in improving my formula?

Source:

Summary:

I would want to drag down the rows in "My summary" and the formula can do the columns+1 automatically. Appreciate for your help.

Here's my formula: =IFERROR(INDEX(Sheet1!$F$9:$F$16, SMALL(IF(Sheet1!$H$9:$H$16="X",ROW(Sheet1!$H$9:$H$16)-ROW(Sheet1!H$9)+1),COLUMN(A1))),"")

read the following article and you will get an idea how to use indirect and address functions to achieve what you need ablebits.com/office-addins-blog/2015/02/10/…. — Atanas Atanasov
@AtanasAtanasov Indirect() is volatile and should be avoided. It's rarely needed, anyway. Index() can do the job much more efficiently. — teylyn
Please post your formula as text, not as a screenshot, so people don't have to type it to make changes to it. — teylyn

teylyn teylyn · Accepted Answer · 2020-10-24T22:10:43

In your screenshot it's difficult to tell which rows the data lives in. The following formula has been entered into Sheet2, cell B2 and copied down.

=IFERROR(TRANSPOSE(FILTER(Sheet1!$F$9:$F$16,INDEX(Sheet1!$H$9:$K$16,1,ROW(A1)):INDEX(Sheet1!$H$9:$K$16,8,ROW(A1))="x")),"")

Source screenshot Sheet1

Result screenshot Sheet 2

This approach uses Excel's Dynamic Array functionality, available only in Excel license with Office 365. The formula is only in column 2. It automatically spills its results into the neighboring columns as required.

edit after comment

If you don't have Office 365, you can use a variant of your formula, where the Index is replaced by and Index():Index() technique. The first Index pulls the first row, the second Index pulls the last row and the two results are combined with the range operator : that combines the two results into a range.

=IFERROR(INDEX(Sheet1!$F$9:$F$16,
SMALL(IF(INDEX(Sheet1!$H$9:$K$16,1,ROW(A1)):
INDEX(Sheet1!$H$9:$K$16,8,ROW(A1))="X",
ROW($A$9:$A$16)-ROW(A$9)+1),COLUMN(A1))),"")

Start in the first result cell (B2 in my screenshot) and use Ctrl+Shift+Enter to confirm, since it is an array formula. Then copy across and down.

Note: screenshot results are different, since I recreated the sample from scratch and the source data is different.

Is there a way to drag formula down but changing the column

1 Answers