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 ...
Formulas and Functions
  • djclements's avatar
    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...

AndyT410's avatar
AndyT410
Brass Contributor
Thanks Peter,
I like this a lot but it falls over if the new import size is larger than the original. The Trusts also needs to be dynamic.
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 04, 2024

AndyT410 

The standard way of dealing with changing input data is to convert the input ranges to Excel tables.  Then the defined names (or structured references) adjust to match the actual data.  Pasting the additional trusts at the bottom of the table should cause it to expand and the formula results to update.

 

Without the Table it would be a case of redefining the named ranges so that they correspond to the data you wish to analyse.

Resources