• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validate table XLookup


New Member

I'm using Office 365. I am trying changing how my dependent lists work with the use of 'Xlookup' instead of the current 'Indirect'. Making it easier to modify the master items list without worrying about defined names range changing.

I was trying to follow the linked tutorial but my result with 'xlookup' would only return the first result but, 'indirect' would return the full list.

this is what I am going for, but as a drop down list.
=XLOOKUP([@[Size / Type]],LIST[Size / Type],LIST[SAP Description])

in the attached file row 7 uses 'indirect', 8 & 9 try to use 'xlookup'



GraH - Guido

Well-Known Member
this formula returns the range, but it can't be used inside data validation. One needs to use the name manager.
=XLOOKUP(BOM[@[Size / Type]];LIST[Size / Type];LIST[SAP Description]):XLOOKUP(BOM[@[Size / Type]];LIST[Size / Type];LIST[SAP Description],,,-1)
Learned a new indirect trick here. Thanks for that :cool: .

EDIT: the tutorial you refer too uses a horizontal lookup and returns the list below the column header. Your set-up is different, thus it does not work.


Last edited:


New Member
Hi GraH,

This is still worlds better! I never though of putting the formula in the name manager.

Thank you