Issue
When using formulas like Xlookup or Index inside the same Excel table, i.e. within a column of the table itself), the formula returns a #NAME? error after being copied into other cells.
After the report is recalculated, or when re-opening the file, the value is displayed correctly.
Cause
Excel automatically changes structured references within tables. For example:
- =XLOOKUP("*Annual Account*", Jobs[Name], Jobs[Name], "Not found", 2)
when entered inside the Jobs table, will be changed to:
- =XLOOKUP("*Annual Account*", [Name], [Name], "Not found", 2)
This formula displays a #Name? error, though after the report is recalculated, displays the value correctly.
Solution
When the report is reopened, or recalculated by pressing F9, the value is displayed correctly.
To resolve the issue, you will need to use different formulas that don't rely on structured references within the same table, for example, by using cell references instead, e.g. F2:F1000.