Lambda isnumber search of dynamic list

[AndyT410]

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 of the long cumbersome formula I am using below but I can't seem to find a way to shorten it. I have been looking at Lambda and sequence without success. Is it possible?

The result I am looking for is consolidated list account number, account name and relevant trust where there is one.

Thanks.

=IF(ISNUMBER(SEARCH(E$2,Import!$C7)),E$2,IF(ISNUMBER(SEARCH(E$2,Import!$D7)),E$2,IF(ISNUMBER(SEARCH(E$3,Import!$C7)),E$3,IF(ISNUMBER(SEARCH(E$3,Import!$D7)),E$3,IF(ISNUMBER(SEARCH(E$4,Import!$C7)),E$4,IF(ISNUMBER(SEARCH(E$4,Import!$D7)),E$4,IF(ISNUMBER(SEARCH(E$5,Import!$C7)),E$5,IF(ISNUMBER(SEARCH(E$5,Import!$D7)),E$5,IF(ISNUMBER(SEARCH(E$6,Import!$C7)),E$6,IF(ISNUMBER(SEARCH(E$6,Import!$D7)),E$6,IF(ISNUMBER(SEARCH(E$7,Import!$C7)),E$7,IF(ISNUMBER(SEARCH(E$7,Import!$D7)),E$7,IF(ISNUMBER(SEARCH(E$8,Import!$C7)),E$8,IF(ISNUMBER(SEARCH(E$8,Import!$D7)),E$8,IF(ISNUMBER(SEARCH(E$9,Import!$C7)),E$9,IF(ISNUMBER(SEARCH(E$9,Import!$D7)),E$9,IF(ISNUMBER(SEARCH(E$10,Import!$C7)),E$10,IF(ISNUMBER(SEARCH(E$10,Import!$D7)),E$10,IF(ISNUMBER(SEARCH(E$11,Import!$C7)),E$11,IF(ISNUMBER(SEARCH(E$11,Import!$D7)),E$11,IF(ISNUMBER(SEARCH(E$12,Import!$C7)),E$12,IF(ISNUMBER(SEARCH(E$12,Import!$D7)),E$12,IF(ISNUMBER(SEARCH(E$13,Import!$C7)),E$13,IF(ISNUMBER(SEARCH(E$13,Import!$D7)),E$13,IF(ISNUMBER(SEARCH(E$14,Import!$C7)),E$14,IF(ISNUMBER(SEARCH(E$14,Import!$D7)),E$14,IF(ISNUMBER(SEARCH(E$15,Import!$C7)),E$15,IF(ISNUMBER(SEARCH(E$15,Import!$D7)),E$15,IF(ISNUMBER(SEARCH(E$16,Import!$C7)),E$16,IF(ISNUMBER(SEARCH(E$16,Import!$D7)),E$16,IF(ISNUMBER(SEARCH(E$17,Import!$C7)),E$17,IF(ISNUMBER(SEARCH(E$17,Import!$D7)),E$17,IF(ISNUMBER(SEARCH(E$18,Import!$C7)),E$18,IF(ISNUMBER(SEARCH(E$18,Import!$D7)),E$18,IF(ISNUMBER(SEARCH(E$19,Import!$C7)),E$19,IF(ISNUMBER(SEARCH(E$19,Import!$D7)),E$19,IF(ISNUMBER(SEARCH(E$20,Import!$C7)),E$20,IF(ISNUMBER(SEARCH(E$20,Import!$D7)),E$20,IF(ISNUMBER(SEARCH(E$21,Import!$C7)),E$21,IF(ISNUMBER(SEARCH(E$21,Import!$D7)),E$21,IF(ISNUMBER(SEARCH(E$22,Import!$C7)),E$22,IF(ISNUMBER(SEARCH(E$22,Import!$D7)),E$22,IF(ISNUMBER(SEARCH(E$23,Import!$C7)),E$23,IF(ISNUMBER(SEARCH(E$23,Import!$D7)),E$23,IF(ISNUMBER(SEARCH(E$24,Import!$C7)),E$24,IF(ISNUMBER(SEARCH(E$24,Import!$D7)),E$24,IF(ISNUMBER(SEARCH(E$25,Import!$C7)),E$25,IF(ISNUMBER(SEARCH(E$25,Import!$D7)),E$25,IF(ISNUMBER(SEARCH(E$26,Import!$C7)),E$26,IF(ISNUMBER(SEARCH(E$26,Import!$D7)),E$26,""))))))))))))))))))))))))))))))))))))))))))))))))))

[#2]

@AndyT410 

That could be like

=XLOOKUP(
    XLOOKUP( A2,Import!$E$2:$E$38,Import!$C$2:$C$38), $E$2:$E$26, $E$2:$E$26,
    XLOOKUP(
        XLOOKUP(A2,Import!$E$2:$E$38,Import!$D$2:$D$38), $E$2:$E$26, $E$2:$E$26, "" ) )

see in column J in attached

[#3 · Accepted solution]

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

[#4]

Thanks, and if I indirect the end cell for the lookup value with a last cell with data count it will make it dynamic.

[#5]

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

[#6]

@AndyT410 

Sure. You may use structured tables, or dynamic ranges, or TOCOL() to eliminate blanks.

[#7]

@AndyT410 

An even more 'packaged' solution!

"Worksheet formula"
= BYROW(customerNames, IdentifyTrustλ(trusts))

where 'customerNames' refers to the Imported range of customer names 1 and 2 and 'trusts' is the list of trusts on sheet1.  That leaves 'IdentifyTrustλ' to be defined.

"IdentifyTrustλ"
= LAMBDA(trusts, 
    LAMBDA(names, 
      @FILTER(trusts, BYROW(ISNUMBER(SEARCH(trusts, names)), OR), "")
    )
  )

p.s. I have modified the workbook to avoid the use of the eta reduced lambda, replacing OR by the lambda function ORλ.

[#8]

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.

[#9]

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?

[#10]

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

[#11]

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

[#12]

Thanks. That works great.