Can't use XLOOKUP/VLOOKUP in a table in MS Excel

Copper Contributor


This is my formula. INDEX7 is the name of the table.


This works fine in a range. But when I convert the range to a table, it stops working (even though it fits the row). And I get a "#SPILL!" error.


Any idea what I can do alternatively?



2 Replies

@VarunAgw Indeed, you can not use dynamic array formulas inside a structured table. You need to write the formulas separately. One for each column or, as you already have discovered,  work in a regular range.

Didn't realize that it allows individual formula for each cell :D