Use cell data to change the formula of table cell reference

[gberg]

I have a table that has headers "Data", "2024", "2023" and "2022". I can use a formula =[@2022] that will result in 111. I want to be able to change the value in the "Data Reference" cell so when I change it to 2023 or 2024 it will return 777 or 555 respectively. I tried =INDIRECT("[@"&B1&"]") but get a #REF! error.

Any help would be appreciated

Data Reference	2022
Data	2024	2023	2022
=[@2022]​	555​	777​	111​

 

[DanteAmor]

Use the name of table:

varios 20ene2024.xlsm
	A	B	C	D
1	Data Reference	2023
2	Data	2024	2023	2022
3	777	555	777	111
Hoja3

Cell Formulas
Range	Formula
A3	A3	=INDIRECT("Table1[@"&B1&"]")

 

[Fluff]

A non volatile approach

=XLOOKUP(B1&"",Table1[[#Headers],[2024]:[2022]],Table1[@[2024]:[2022]])

 

[Alex Blakenburg]

If you modify Fluff's formula to take in the entire table's columns that will save making it dependant on the current year headings

=XLOOKUP($B$1&"",Table1[#Headers],Table1[@])

PS: All options need B1 as absolute addressing $B$1.

 

[gberg]

I'm always amazed at how smart people are on this board. Tanks to everyone for the help, Alex your solution works great. I can add additional years and don't need to rewrite all formulas for the added years! This is great!

 

[Alex Blakenburg]

Thanks for the feedback. Glad we could help.