Forum Discussion

bertofivee's avatar
bertofivee
Copper Contributor
Mar 27, 2024
Solved

Can't fix #REF! with INDEX

Hi all, I need help to get rid of this #REF! when using the INDEX function.   I have two Excel files with some sheets; in both, I get an error when I try to use the INDEX function, even with a new ...
  • HansVogelaar's avatar
    HansVogelaar
    Mar 27, 2024

    bertofivee 

    Thanks! The table on the INDEX sheet has been named Index:

    So when you use Index in a formula, Excel thinks you refer to the table, but then you cannot use it as a function.

    You can rename the table:

    • Click in any cell of the table on the INDEX sheet, for example cell A5
    • Activate the Table Design tab of the ribbon.
    • Edit the name in the Table Name box (see screenshot)

    Switch to the second sheet. You'll see that the formula uses the edited name:

    Change it to =INDEX(A1:A3,1). The formula will then work as intended.

Resources