Forum Discussion

DevoutSkeptic's avatar
DevoutSkeptic
Copper Contributor
Oct 03, 2026

What's wrong with my XLOOKUP formula?

I'm trying for the first time to define cells in an Excel worksheet with values selected from list, which is defined by a table. The table is in the same worksheet, to the right of my data columns. I was OK until I tried to make the cell look up the value in one table column (an ID) and display the corresponding value from another column.

I'm defining the cell with data validation, type list, using this expression for source:

=XLOOKUP(D2,CatTable[ID],CatTable[Categories])

Where D2 is a data cell, CatTable is the table's name, and ID and Categories are respectively the names of the ID and display columns.

When I click OK, Excel says "There's a problem with this formula" (followed by some useless stuff that assumes I don't know what a formula is and entered one by mistake). What is wrong?

Error
excel

1 Reply

  • Harun24HR's avatar
    Harun24HR
    Silver Contributor
    Oct 05, 2026

    Are you using XLOOKUP() inside data validation custom formula? Better to attach a small sample file here in post or share via OneDrive or GoogleDrive and explain what you are trying to do.