Referencing a table range, based on a column name

Question

I am attempting to alter a spreadsheet that currently uses INDIRECT to dynamically determine a column range in a table. It's really slow.

I have a vba function that will determine the column name, but I now need to reference the column identified by that name. The end goal is to use it in a SUMIF statement.

Currently uses: =SUMIFS(My_Table_Name[UNIT_TOTAL],INDIRECT(MyNamedItem),1)

The value of MyNamedItem is My_Table_Name[THE_COLUMN]

I can return My_Table_Name[THE_COLUMN] as a string by a function, but then can't pass that into the SUMIF as an argument. How do I return it as a table column range which can be passed as an argument?

The formula could then be: =SUMIFS(My_table_name[UNIT_TOTAL],My_function_Name(),1)

Both INDIRECT and OFFSET volatile functions can almost always be replaced by the non-volatile INDEX. Can you provide a minimal reproducible example that no one has to retype from an image? — user4039065
– user4039065, Commented Apr 3, 2018 at 1:28
Please use formatting tools to properly edit and format your question/answer. Codes within sentences are to be formatted as code Very Important words to be bold , lesser important onces Italic Also use lists if necessary — Morse
– Morse, Commented Apr 3, 2018 at 1:30

teylyn · Accepted Answer · 2018-04-03 01:46:47Z

1

Consider this screenshot:

The formula in cell K2 is

=SUMIFS(Table1[Unit Total],INDEX(Table1,,MATCH(I2,Table1[#Headers],0)),J2)

copied down. The table column is calculated dynamically with an Index/Match combo.

answered Apr 3, 2018 at 1:46

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user9588528 Over a year ago

Thank you, that's great. I will just replace with INDEX.

Collectives™ on Stack Overflow

Referencing a table range, based on a column name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related