Search code examples
excelexcel-formulaexcel-2013

Structured Reference in an Index/Match Calculated Column Formula for Header Cell Range in a Table

I have this formula in a calculated column that is working great:

=IFERROR(INDEX(Allocation_of_Funds[[#Headers],[End.Nursing]:[Unassigned14]],MATCH(TRUE,INDEX(Allocation_of_Funds[@[End.Nursing]:[Unassigned14]]>0,0),0)),"")

But this formula is giving me trouble and represents what I want in the next calculated column, (based on the value in the previous column above) but it returns a #REF! error:

=INDEX(INDIRECT("Allocation_of_Funds[[#Headers]"&"["&[@[SOURCE 1]]&"]"):[@Unassigned14],MATCH(TRUE,INDIRECT("Allocation_of_Funds[[#Headers]"&"["&[@[SOURCE 1]]&"]"):[@Unassigned14]>0,0),0)

The details of the tables setup is as follows, in case it's helpful:

I have a table with a range of columns and each column represents a different type of account. For each row, any combination of these columns could contain values or blanks, so I've got another set of columns that I want to identify the table column headers for the non-blank columns for each record.

SOURCE1 | SOURCE2 | SOURCE3 | ACCT1 | ACCT2 | ACCT3 | ACCT4 | ACCT5
ACCT1   | ACCT2   | ACCT4   | 500   | 300   |       | 100   | 
ACCT2   | ACCT3   |         |       | 200   | 100   |       |       
ACCT3   |         |         |       |       | 500   |       | 
        |         |         |       |       |       |       | 
ACCT3   | ACCT4   | ACCT5   |       |       | 200   | 300   | 50
ACCT1   | ACCT3   | ACCT4   | 123   |       | 332   | 100   |

So I need the SOURCE2 column to use the value in the SOURCE1 column to identify the start of the range where I am looking for the next cell with a value, whereby the column header above that value will be returned for the SOURCE2 row value. The same formula will apply to the SOURCE3 column, using the value of the SOURCE2 column to identify the start of the next range.

Thank in advance for picking your brain!

-Lindsay

Solution

  • I used the following formula to pull the headers and place them under the source numbers:

    =IFERROR(INDEX($D$1:$H$1,AGGREGATE(15,6,COLUMN($D2:$H2)/ISNUMBER($D2:$H2)-COLUMN($D$1)+1,RIGHT(A$1,1)*1)),"")

    I assumed your table's top left corner was in A1 with 1 being the header row and A-C being your source columns and D to H being account columns. The above formula can be placed in cell A2 and copied to the right and down as need be.

    You seem to have a grasp of the IFERROR and INDEX function so I will explain the AGGREGATE function:

    =AGGREGATE(15,6,COLUMN($D2:$H2)/ISNUMBER($D2:$H2)-COLUMN($D$1)+1,RIGHT(A$1,1)*1)

    The AGGREGATE function is a mixture of a bunch of different functions rolled into with the ability to ignore some calculations. Another added feature is that some of the built in functions perform array calculations without the need for arrays.

    In this particular case I chose aggregate function 15 which is the same as the SMALL function. I have also told aggregate to ignore calculations which generate errors by using the "6". For the array calculation I have asked it to divide the column number it is working with by the True or False result of that column being a number:

    COLUMN($D2:$H2)/ISNUMBER($D2:$H2)-COLUMN($D$1)+1

    True in excel math is the same as 1 and False is the same as 0. Anytime the cell is not a number it will try to divide by zero, generate an error, and be ignored by Aggregate function. This basically generates a list of column numbers that meet the criteria of having a number in their column. The subtraction of the D1 followed by a +1 is to convert the column number that is determined, to a relative column under your accounts headers.

    The next part of the aggregate function is telling the SMALL operation which number in sorted order needs to be returned. I used the last character in your source header to determine which column number to return. For SOURCE1 the last character is 1 so I want the smallest column number returned. For SOURCE2, the second smallest number is returned. The *1 at the end converts the character to a number instead of 1 as text.

    RIGHT(A$1,1)*1

    Ergo, if you want to use up to 9 sources you can. You can do more sources as well but you would need to revise this formula or come up with a different way of providing which number of the small list you want returned. And you can expand the D2:H2 reference to be all your accounts, and adjust the D1:H1 reference to cover all your account headers.

    Proof of Concept

    enter image description here