I’ve been working with the new Tables feature in Google Sheets and I’m running into some issues with cell referencing. I know that I can reference an entire column using syntax like Products[Price] which works great.
However, I’m trying to figure out how to reference a specific cell in the current row, similar to how Excel handles this. In Excel, you can use something like VLOOKUP(Products[@Item], Products[Item], 1, FALSE) where the @ symbol indicates you want the value from the current row.
When I try using the @ symbol in Google Sheets Tables, I get an error message. Has anyone found a way to reference individual cells within a Table structure using the column headers? I need to create formulas that work with the current row’s data without having to use traditional cell references like A2 or B3.
Here’s what I do - mix table functions with regular referencing. Skip the @ notation since it doesn’t work reliably. Use INDEX and MATCH to grab the current row instead: INDEX(Products[Price], MATCH(ROW(), ROW(Products[Item]), 0)) pulls the price for whatever row you’re on. OFFSET works too: OFFSET(Products[Price], ROW()-MIN(ROW(Products[Price])), 0, 1, 1) gets the same result without hardcoded cell references. Yeah, it’s messier than Excel’s way, but it gets the job done.
Google Sheets doesn’t support the @ symbol for current row references like Excel does. I’ve had success using INDIRECT with ROW() to dynamically reference the current row. Instead of Products[@Item], try INDIRECT(“Products[Item]” & ROW()-1) if your table starts at row 2. You can also use structured references with FILTER - like FILTER(Products[Price], Products[Item]=INDIRECT(“B” & ROW())) where B is your item column. It’s wordier than Excel but works. Just combine Google Sheets’ structured table references with regular row/column functions.
yeah, google sheets is frustrating with this. i just mix regular cell references with table syntax - like VLOOKUP(B2, Products[Item:Price], 2, false) where B2 is the current row’s item. not as clean as excel’s @ but it works without needing INDIRECT.