Data Validate table XLookup

[Patrick]

Hello,

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'

https://excelexciting.com/how-to-make-dependent-dropdown-list-with-xlookup/

 

[GraH - Guido]

Hi,
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 .

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.

 

[Patrick]

Hi GraH,

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

Thank you