LET formula to spill dynamically

[MurdochQuill]

Hi all,

I have another question regarding =LET.

I currently use this let formula

=LET(Drng,'Sheet1'!$A$1:$A$20,Rng,F2,IF(SUM((Drng<>"")*(ISNUMBER(MATCH("*"&Drng&"*",Rng,0)))), "Y", "N"))

This works fine when I fill down. I'm wondering how to change "F2" in this formula to reference the current row, and to also make this formula spill according to my global variable "MaxRows"
EG, the formula should then cover from G2:G52 (As the value of MaxRows is 52).

For future reference, could someone run through the steps to make it do this? I'm still not quite understanding how to make it work each time.

 

[jasonb75]

When I did a quick test on my suggestion before posting it it appeared to work as needed, with additional testing I can see that the MIN function is looking at the entire array and not just the current row as it appeared to be doing in my initial test.

Doing the reverse of what you are attempting with yes / no against the match string instead of the mixed code list would be fairly easy, your formula would work with the edit suggested in post 2. With the way that you want it I think that it is going to be over complicated to make it spill, that is if it is even possible.

 

[MurdochQuill]

When I did a quick test on my suggestion before posting it it appeared to work as needed, with additional testing I can see that the MIN function is looking at the entire array and not just the current row as it appeared to be doing in my initial test.

Doing the reverse of what you are attempting with yes / no against the match string instead of the mixed code list would be fairly easy, your formula would work with the edit suggested in post 2. With the way that you want it I think that it is going to be over complicated to make it spill, that is if it is even possible.

I went back and reversed it, but I'm still confused.
Am quite stuck on making it spill but not compare the entire array to the cell.

 

[ISY]

Hi

Excel 365 Beta
Here's how I get the list

=BYROW(IFERROR(SEARCH(TRANSPOSE(H2:H5),F2:INDEX(F:F,J2)),0),LAMBDA(array,IF(SUM(array)>0,"YES","NO")))

 

[jasonb75]

I went back and reversed it, but I'm still confused.

In case you misunderstood what I meant by reversing it, I was referring to what I have done in column F below. Where there are arrays in both sides of the formula, you can easily match a short string to a long one but not a long string to a short one.

Am quite stuck on making it spill but not compare the entire array to the cell.

The problem that you have is that comparing the arrays requires functions that prevent the formula from spilling and any attempt at a workaround causes incorrect results. The formula in column B is the closest that I have found so far. It returns the correct results when evaluated but for some reason is posting the wrong result to the cells in a couple of rows.

In all honesty, I think that this formula is unspillable, at least with the current stable versions of excel. When the new functions from the current beta release are made available to all then it will more likely be possible (as per @ISY's suggestion in post 13, I do not have the beta version to test that formula but it looks promising).

MurdochQuillLet.xlsx
	A	B	C	D	E	F	G	H
1	Code found?	Code found? Spill to maxrows	Mixed codes		Match String	Found?		Maxrows
2	YES	Y	sdaHello1dsfsd		Hello1	Y		10
3	NO	N	34f43t4		Hello2	N		MatchRows
4	NO	N	54y54y5		3Bye	N		5
5	YES	Y	dsfsHello1mbmbmb		2Bye	Y
6	YES	Y	etjrjyrjty2Bye
7	NO	N	sjfdgnjs
8	NO	Y	shthmrhkBye
9	NO	Y	safreHello
10	YES	Y	dsggmkmkm 2Bye 1111
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=LET(Rng,C$2:INDEX(C:C,MaxRows),IF(MIN(LEN(SUBSTITUTE(@Rng,TRANSPOSE(E$1:INDEX(E:E,MatchRows)),"")))<LEN(Rng),"Y","N"))
F2:F5	F2	=IF(ISNUMBER(XMATCH("*"&E2:INDEX(E:E,MatchRows)&"*",C2:INDEX(C:C,MaxRows),2)),"Y","N")
H4	H4	=MATCH("zzz",E:E)
A2:A10	A2	=IF(SUM(($E$2:$E$10<>"")*(ISNUMBER(MATCH("*"&$E$2:$E$10&"*",INDEX(C:C,ROW()),0)))), "YES", "NO")
Dynamic array formulas.