Indirect reference to column in a table

[KrsOne20]

I have a table where all references to columns are stated as [@[COLUMNNAME]] (I use Excel +2010)

Depending on value in column X, I want to return column 1, 2 or 3 in my lookup. I want to avoid using multiple if statements for this purpose. Instead I want to create a dynamic INDIRECT reference to column.

For example if column priority is "quarter'', it should return Q4-2017 (the reference in Excel is =[@[Quarter key]]. My current indirect reference is

=INDIRECT("[@["&[@Priority]&" key]]")

, however it keeps returning error #REF

When I just enter my formula without the Indirect function (just entering ="[@["&[@Priority]&" key]]"), I do get =[@[Quarter key]].
So what am I doing wrong?

Also added extract of file as attachment
https://sheet.zoho.com/sheet/editor...f121d7b851ff7b920cc106ed40003a0c0ae6a6466008b

 

[KrsOne20]

You can also access file from here if the above link doesnt work
https://ufile.io/wz2gd

 

[Eric W]

The problem seems to lie in the table name. This works:

=INDIRECT("Table1["&[Priority]&" key]")

This also works:

=INDIRECT($R$6&"["&[Priority]&" key]")

if you put "Table1" in R6.

But you can't remove Table1 from the formula, or use @ as a shortcut. Probably INDIRECT tries to resolve the reference without "knowing" where the formula is.

You could also do something like this:

=CHOOSE(MATCH([Priority],{"Quarter","Semi-Annual","Annual"},0),[Quarter key],[Semi-annual key],[Annual key])

 

[KrsOne20]

The problem seems to lie in the table name. This works:

=INDIRECT("Table1["&[Priority]&" key]")

This also works:

=INDIRECT($R$6&"["&[Priority]&" key]")

if you put "Table1" in R6.

But you can't remove Table1 from the formula, or use @ as a shortcut. Probably INDIRECT tries to resolve the reference without "knowing" where the formula is.

You could also do something like this:

=CHOOSE(MATCH([Priority],{"Quarter","Semi-Annual","Annual"},0),[Quarter key],[Semi-annual key],[Annual key])

Awesome, thanks! I tried the first and CHOOSE function, both worked flawless. Didnt know about the CHOOSE function, seems very usefull will use it now more often.

 

[Eric W]

Glad to help!