CODEWITHSUNDEEP
excelexcel-formula
Jan
Jan

Reputation: 11

Create dynamic table & column reference without INDIRECT

I'm using following dynamic table reference several times in my workbook which is causing performance issues:

SUMIFS(INDIRECT(VLOOKUP($A$1,$A$2:$B$4,2,0)&"[kpi_name]"),INDIRECT(VLOOKUP($A$1,$A$2:$B$4,2,0)&"[filter1]"),UPPER($H13))

based on the input in A1 I need to switch between table1, table2 and table3.

I do this by building a vlookup map in A2:B4 that return the appropiate table names and using this + column name in a indirect formula.

I would like to move to a better solution using INDEX or another named range. I tried to use a named range with a formula returning the right table reference:

=IF(A1=1,table1,IF(A1=2,table2,IF(A1=3,table3,0)))

But this way I can't use named_range[column of table]

Also I can't get a working solution with INDEX...

Upvotes: 1

Views: 1242

Answers (1)

Jan
Jan

Reputation: 11

Thanks to the comments of the initial question I came up with follow:

SUMIFS(INDEX(CHOOSE(A1,TABLE1,TABLE2),,MATCH(VAR_COLUMN_NAME,INDEX(CHOOSE(A1,TABLE1,TABLE2),1,0),0)),INDEX(CHOOSE(A1,TABLE1,TABLE2),,MATCH("filter1",INDEX(CHOOSE(A1,TABLE1,TABLE2),1,0),0)),UPPER(ABC))

Upvotes: 0

Related Questions