Forum Discussion
how to refer to a dynamic array in INDEX
I am doing a task where I need to look up the number of procedures from Col D for certain year (Col A), location (Col B) and a type of procedure (Col C) and put them into a table on a separate sheet. The number of rows in the data table increases as new data added (it is connected to a database and I refresh the connection periodically).
Data looks like:
I would like my arrays also change in the INDEX / MATCH formula, so I tried this one:
{=INDEX(OFFSET(Data!$D$1,,,COUNTA(Data!D:D)), MATCH(1,(2017=OFFSET(Data!$A$1,,,COUNTA(Data!A:A)))*(6=OFFSET(Data!$B$1,,,COUNTA(Data!B:B)))*(5=OFFSET(Data!$C$1,,,COUNTA(Data!C:C))),0))}
The formula returns #REF, but when I replace the reference for the first INDEX argument for hard reference as follows:
{=INDEX(Data!$D$1:$D$12, MATCH(1,(2017=OFFSET(Data!$A$1,,,COUNTA(Data!A:A)))*(8=OFFSET(Data!$B$1,,,COUNTA(Data!B:B)))*(7=OFFSET(Data!$C$1,,,COUNTA(Data!C:C))),0))},
it works. There is another issue with the first formula. If I change the year or location code or procedure code, it also changes in adjacent cell where the same formula was copied. This doesn’t happen with the second formula.
Does anybody has any idea why is that and how to fix it?
- It appears from the screenshot you posted that you are using Excel Tables.
If you have excel 2007 or any version 2010, 2013, 2016 or 365 then it would be incorrect to use these extra lines and referencing the whole column inside your formula which will suck up the processor with heavy calculating millions of rows.
Instead you could use the table structural references inside your formulas and one of features of Excel Tables is fulfill the requirement of dynamic range meaning that if your table expands or collapses, your formulas will encompasses the revised range in the tables without the need to modify the formula.
so instead of using this OFFSET(Data!$D$1,,,COUNTA(Data!D:D))
use the Table column reference.Yes, I did. It works, thank you.
