google-sheetsgoogle-sheets-formula

Using INDIRECT in Google Sheets to reference to reference a table's named column

My use case is more complex than this but I think this example illustrates my problem clearly.

I have a simple table in Google Sheets. It is called test.

Google Sheets screenshot with 3 columns, 1st with simple 3 letter names titled "Name", 2nd with integers titled "1st Number", 3rd with integers titled "2nd Number"

Name 1st Number 2nd Number
Hat 1 2
Cat 4 8
Mat 16 32

I am able to return the sum of the values in its 2nd column by using the formula: =sum(test[1st Number])

However, I am unable to copy that exact string of test[1st Number] into a cell to reference it using INDIRECT =sum(indirect(B1)) to get the sum of that column.

The error I am getting is:

Function INDIRECT parameter 1 value is 'test[1st Number]'. It is not a valid cell/range reference.

Screenshot of same spreadsheet as above, but with the addition of a few rows above the table showing a REF error and a cell above it containing the string that was being used in the INDIRECT formula that caused the REF error

I am hoping that there is an error in my formula rather than it being impossible to reference a named column in a table using INDIRECT.

Solution

I used index and match as recommended by @doubleunary below to have the formula

=sum(index(test, 0, match(C1, test[#HEADERS], 0)))

referencing the string 1st Number in cell C1

enter image description here

Solution

  • Structured table references are not currently (December 2024) supported by the indirect() function. Use this pattern instead:

    =sum(index(Table1, 0, match(B1, Table1[#HEADERS], 0)))

    ...where cell B1 matches the name of the column you want to sum.

    See Use table references in Google Sheets.