Forum Discussion
XLOOKUP IN LIST OPTION OF DATA VALIDATION
PC/Windows 10/Excel 365
This appears to be a simple issue with a simple solution - then again, there may be no solution. Been trying to understand for some time why it doesn't work. Consequently, would appreciate it if anyone could show or explain to me what's wrong.
Basically, when I use XLOOKUP formula (in List option of Data Validation) on an array it displays the "named range" INSTEAD of the data list associated with the "named range" (see attached spreadsheet). I temporarily solved the issue by using nested IF functions, however, I'm limited to 255 characters and I would need much more than that in my actual data spreadsheet.
I've attached a fictitious simple spreadsheet to demonstrate my issue. The actual spreadsheet is a lot more involved. Once again, I would be sincerely grateful if you could point out the error of my way or perhaps suggest a different approach. Thank you for whatever insight you can provide.
Regards
CAROM16 The XLOOKUP formula returns the name of the range that relates to the day of the week. Then you need to wrap it in an INDIRECT function to tell Excel to display the content of that named range and not just the name itself.
Enter the following in the List box and it shall work.
=INDIRECT(XLOOKUP($C5,DOW,DOWM))
4 Replies
- Thank you, Thank you, Thank you !!!!!!!!!!!!! I toiled on this for weeks knowing there had to be a simple solution. I believe I did try the INDIRECT function at some point. Obviously, I must have worded it wrong. It works like a charm now. Thank you, thank you, thank you!!!!!!!!!!!!
CAROM16 The XLOOKUP formula returns the name of the range that relates to the day of the week. Then you need to wrap it in an INDIRECT function to tell Excel to display the content of that named range and not just the name itself.
Enter the following in the List box and it shall work.
=INDIRECT(XLOOKUP($C5,DOW,DOWM))
Riny_van_Eekelen Thank you, Thank you, Thank you !!!!!!!!!!!!! I toiled on this for weeks knowing there had to be a simple solution. I believe I did try the INDIRECT function at some point. Obviously, I must have worded it wrong. It works like a charm now. Thank you, thank you, thank you!!!!!!!!!!!!
