Search code examples
excelexcel-formulaexcel-2007

HLOOKUP on external data table - wrong header row

Trying to run a Hlookup on a table that is linked to a query in a MS database. The data table is in a separate tab and starts in row 3, and includes a header row. So headers are in row 3, data is from row 4 onwards. data table is named My_Table My HLOOKUP formula is;

=HLOOKUP("Product1",My_Table,A3,false)

Product1 is the actual header of the column to find, and the cell A3 contains the row number for the lookup. However, this didn't return the data expected. Stepping through the evaluate formula function, Excel is calculating the table to begin in row 4, so is looking for the header in the wrong row (ie row 4). Checking the name manager, the table is locked and shows as starting in row 3.

Has anyone encountered/found a way around this? I would like to keep the table as a table so that it dynamically extends rows as needed based on the results of the query.

Solution

  • To reference the whole table, use

    =HLOOKUP("Product1",My_Table[#All],A3,FALSE)

    This is called Structured Referencing

    Other examples

    • The entire table =TableName[#All]
    • The table headers =TableName[#Headers]
    • To the entire column =TableName[[#All], [ColumnName]]
    • The header value of a column =TableName[[#Headers], [ColumnName]]
    • The same row in the table =TableName[[#This Row][ColumnName]]
      • In Excel 2010 or later =TableName[@ColumnName]
    • Heading of table =TableName[#Headers]
    • Entire table (2) =TableName[#All]
    • Table total row =TableName[#Totals]