Forum Discussion
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?
3 Replies
The XLOOKUP formula is valid, but it is in the wrong place. A Data Validation list Source must supply selectable items; your XLOOKUP returns one category for D2. If the dropdown should show IDs, set its source to the ID column through a workbook name referring to CatTable[ID]. Then put this formula in a separate display cell: =XLOOKUP(D2,CatTable[ID],CatTable[Categories],"Not found"). If users should choose category text instead, make the validation source the Categories column and omit the lookup. Excel stores the selected item in the validated cell; it cannot store an ID while displaying another category in that cell without VBA or a helper cell. If Excel rejects the structured reference in Data Validation, define a name such as CatIDs referring to =CatTable[ID], then use =CatIDs as the Source. The named reference expands automatically with the table
I wanted to attach the file to my original post, but I didn't see any way to do that, and I still don't. Can you tell me where Microsoft has hidden that capability, please?
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.
