0

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?

Improve this question
2
  • Search for Absolute Structured References in Excel Table Formulas Commented Jul 25, 2022 at 8:32
  • Using the absolute form of structured referencing, as indicated in my answer below, I cannot reproduce your problem. The formula returns information from the Brand column no matter I add or remove columns from the table. Please read HELP for How to create a Minimal, Complete, and Verifiable example. Then edit your question to provide more information. Commented Jul 25, 2022 at 10:55

1 Answer 1

Reset to default
0

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
enter image description here

Swap Car & Year
enter image description here

Insert some columns enter image description here

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

7 Comments

Hi Ron! I have tried to use this approach as well. The problem is not that I have to drag the formula across columns or rows but rather that I want the column reference in the formula to be locked to the column name and not the current position of the column. Do you know how to accomplish this? :-)
@EmilOlesen See my response to your identical comment. Based on the information you have so far provided, it works for me. If it does not work for you, read the link in my comment above and edit your question to provide a reproducible problem.
Okay thanks, I have updated the post with a more thorough description of the problem in an example
@EmilOlesen You did not provide a reproducible example of data and formula. So I have nothing to add.
I have tried writing the data by hand since I cannot upload pictures, but the structure of the Output changes. I hope it makes it more reproducible
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.