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

Sample.xlsx22 KB

Formulas and Functions

djclements

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

Sample2.xlsx16 KB

djclements

Bronze Contributor

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

Sample2.xlsx16 KB

AndyT410

Brass Contributor

Mar 01, 2024

Thanks. This is amazing. I wish I could dream up this dark magic you use :).

Forum Discussion

Lambda isnumber search of dynamic list

Share

Resources