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

AndyT410

Copper Contributor

Mar 04, 2024

Hi Dj,
Is it possible to then filter the results to only show schools that are in a trust with the formula in that same cell?

djclements

Bronze Contributor

Mar 04, 2024

AndyT410 Sure thing, just add the FILTER function to the final argument as follows:

    FILTER(HSTACK(nums, names1, results), results<>"")

The whole formula would then be:

=LET(
    nums, TOCOL(DROP(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, BYROW(ISNUMBER(SEARCH(trusts, a)), LAMBDA(r, XLOOKUP(TRUE, r, trusts, "")))),
    trust1, TRUST(names1),
    trust2, TRUST(names2),
    results, IF(trust1="", trust2, trust1),
    FILTER(HSTACK(nums, names1, results), results<>"")
)

If you want the ability to toggle back and forth between showing all data and the filtered view, you could also define another variable at the top called "showAll", for example, which can quickly be changed from FALSE to TRUE:

=LET(
    showAll, FALSE,
    nums, TOCOL(DROP(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, BYROW(ISNUMBER(SEARCH(trusts, a)), LAMBDA(r, XLOOKUP(TRUE, r, trusts, "")))),
    trust1, TRUST(names1),
    trust2, TRUST(names2),
    results, IF(trust1="", trust2, trust1),
    array, HSTACK(nums, names1, results),
    IF(showAll, array, FILTER(array, results<>""))
)

You could even go as far as linking that variable to another cell in the worksheet controlled by a checkbox, which I didn't include in the attached file because new and improved checkboxes are scheduled to be coming soon to MS365.

Sample3.xlsx16 KB

AndyT410
Copper Contributor
Mar 04, 2024
Thanks. That works great.

Forum Discussion

Lambda isnumber search of dynamic list

Resources