تابعنا

Microsoft Tech Community - Latest Blogs - Excel Blog

الخميس، 5 ديسمبر 2024

Even easier than easiest - Multi Level Dependent Drop Down Lists

 

=LAMBDA(range,[lookup1],[lookup2],[lookup3],[lookup4],[lookup5],[lookup6],[lookup7],[lookup8],[lookup9],[lookup10],

LET(

_s, "%^&&@", 

lookupValue, lookup1 & _s & lookup2 & _s & lookup3 & _s & lookup4 & _s & lookup5 & _s & lookup6 & _s & lookup7 & _s & lookup8 & _s & lookup9 & _s & lookup10,

levelIndex, IFERROR(ROWS(TEXTSPLIT(lookupValue, , _s, TRUE())), 0) + 1, 

lookupArray, BYROW(EXPAND(CHOOSECOLS(range, SEQUENCE(1, levelIndex - 1)), , 10, ""), LAMBDA(row, TEXTJOIN(_s, FALSE, row))),

returnRange, INDEX(range, 0, levelIndex), 

result, IF(ISOMITTED(lookup1) * levelIndex = 1, returnRange, XLOOKUP(lookupValue, lookupArray, returnRange):XLOOKUP(lookupValue, lookupArray, returnRange, , , -1)),

result))



@ExcelOffTheGrid


ليست هناك تعليقات:

إرسال تعليق