Forum Discussion

AndyT410's avatar
AndyT410
Brass Contributor
Mar 01, 2024

Lambda isnumber search of dynamic list

Hi, I have a dynamic list of school trusts and a list of schools names and 1st line of addresses in columns C & D on an import sheet. I am trying to find C & D on the dynamic list. I have a portion ...
  • djclements's avatar
    Mar 01, 2024

    AndyT410 If you're interested in a single-cell dynamic array report, give this a try:

     

    =LET(
        nums, DROP(TOCOL(Import!E:E, 1), 1),
        names1, XLOOKUP(nums, Import!E:E, Import!C:C),
        names2, XLOOKUP(nums, Import!E:E, Import!D:D),
        trusts, UNIQUE(TOROW(DROP(E:E, 1), 1), 1),
        TRUST, LAMBDA(a, LET(b, ISNUMBER(SEARCH(trusts, a)), BYROW(b, LAMBDA(r, XLOOKUP(TRUE, r, trusts, ""))))),
        trust1, TRUST(names1),
        trust2, TRUST(names2),
        results, IF(trust1="", trust2, trust1),
        HSTACK(nums, names1, results)
    )

     

    Please see attached...

Share