Lock Structured References to column names in Excel-formulas when data Query is updated and refreshed

Question

I use references to other columns in my table with the following formula: =TableName[@[ColumnHeaderName]] And use the following as a Table array: TableName[[#All];[ColumnHeaderName]] (this is used if I want to make a HLOOKUP to a specific cell given a row number in ColumnHeaderName)

I have the following Dataset with tablename Cars:

   (A)       (B)
(1)Brand     Year
(2)BMW       2016
(3)Audi      2013
(4)Mercedes  2014

If I am to look up the value of the year column in Row 4 I say =HLOOKUP("Year";Cars[[#All];[Year]];4;FALSE) (=2014)

If the dataset now looks like

   (A)       (B)
(1)Year      Brand     
(2)2016      BMW
(3)2013      Audi
(4)2014      Mercedes

then my formula in Excel says =HLOOKUP("Year";Cars[[#All];[Brand]];4;FALSE)

How can I make sure that my formula always says =HLOOKUP("Year";Cars[[#All];[Year]];4;FALSE) regardless of the position of the 'Year' column in my Cars table?

Answer 1

Try using the Absolute Reference form for Structured References. eg:

=Cars[@[Brand]:[Brand]]

Edit
Now that you have provided data, it seems the syntax of your formula is incorrect.

For the HLOOKUP function, you could be using:

=HLOOKUP("Year",Cars[#All],4,FALSE)

Original Table

Swap Car & Year

Insert some columns