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?

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
Emil Olesen
  • 1
  • 1
  • Search for `Absolute Structured References in Excel Table Formulas` – Ron Rosenfeld Jul 25 '22 at 08: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](http://stackoverflow.com/help/mcve). Then **edit** your question to provide more information. – Ron Rosenfeld Jul 25 '22 at 10:55

1 Answers1

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

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • 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? :-) – Emil Olesen Jul 25 '22 at 10:58
  • @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. – Ron Rosenfeld Jul 25 '22 at 11:02
  • Okay thanks, I have updated the post with a more thorough description of the problem in an example – Emil Olesen Jul 25 '22 at 11:17
  • @EmilOlesen You did not provide a reproducible example of data and formula. So I have nothing to add. – Ron Rosenfeld Jul 25 '22 at 11:19
  • 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 – Emil Olesen Jul 25 '22 at 12:14
  • Okay Thanks! :-) In this solution Cars[Years] is not included and there are no references to coloumns that are able to change. If I have to use Cars[Year] in another more complex formula do you then know how I can make sure that Cars[Year] won't change to Car[Brand] in the formula if the columns change location? – Emil Olesen Jul 25 '22 at 13:13
  • @EmilOlesen During some testing I did with the index function, using the structured absolute reference forms maintained the pointer to the correct column. I suggest if you are having a problem it may be that your other formulas are not structured properly. – Ron Rosenfeld Jul 25 '22 at 13:21