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]

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"

If you want it to spill then you use the entire range, not the current row. Change F2 to F2:INDEX(F:F,MaxRows)

You will also need to remove the SUM function and just have the array on its own, otherwise the results will be aggregated.

 

[MurdochQuill]

If you want it to spill then you use the entire range, not the current row. Change F2 to F2:INDEX(F:F,MaxRows)

You will also need to remove the SUM function and just have the array on its own, otherwise the results will be aggregated.

Thanks for the help!
Yes, I have tried to use this INDEX syntax, however then we will be comparing the entire array "Rng" with "Drng".
I would like to compare the row EG: ROW() where the formula exists. So it would be comparing the active row in "Rng" with the entire array of "Drng".

Any ideas on how to add this into my formula?

 

[MurdochQuill]

Sorry to bump; but does anyone know how to edit this to reference the current row in the dynamic range?

 

[RoryA]

It's not going to spill unless it returns an array of results, which it won't do if you only use one cell as the criterion.

 

[jasonb75]

Any ideas on how to add this into my formula?

Exactly how I said in my reply. Use the INDEX method and remove SUM.

If it doesn't do what you need it to then it is your question that is wrong, not the answer provided. You have said how you are trying to do something but not what you are actually trying to do so it is possible that you are taking an incorrect approach to the task. Without more information or mind reading abilities we could spend a lot of time guessing incorrectly.

 

[jasonb75]

After a bit of head scratching, I think that this might be what you're attempting.

=LET(Drng,TRANSPOSE(Sheet1!$A$1:$A$20),Rng,F2:INDEX(F:F,MaxRows),IF(MIN(LEN(SUBSTITUTE(Rng,Drng,"")))<LEN(Rng),"Y","N"))

 

[MurdochQuill]

Sorry for my lack of elaboration.

I'm trying to make col A spill down like col C is. Col C is returning the column value, which is what I want to replace "f2" on my original formula with.

Here's an example sheet to make it clear what I'm doing

LIST.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Code found?	Code found? Spill to maxrows	Example spill			Mixed codes		Match String		Maxrows
2	YES		$F$2			sdaHello1dsfsd		Hello1		10
3	NO		$F$3			34f43t4		Hello2
4	NO		$F$4			54y54y5		3Bye
5	YES		$F$5			dsfsHello1mbmbmb		2Bye
6	YES		$F$6			etjrjyrjty2Bye
7	NO		$F$7			sjfdgnjs
8	NO		$F$8			shthmrhkBye
9	NO		$F$9			safreHello
10	YES		$F$10			dsggmkmkm 2Bye 1111
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=ADDRESS(ROW(F2:INDEX(F:F,Maxrows))-ROW(F2)+2,6)
A2:A10	A2	=IF(SUM(($H$2:$H$10<>"")*(ISNUMBER(MATCH("*"&$H$2:$H$10&"*",$F2,0)))), "YES", "NO")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Maxrows	=Sheet2!$J$2	C2

 

[jasonb75]

Did you try the formula in post 7?

 

[MurdochQuill]

Did you try the formula in post 7?

Yes. I'm not trying to use a length comparison.
I'm still unsure how to tackle this, as I'm looking for the "Match string" array inside "Mixed codes" per row. The Match string array is on another page usually, which is just a bunch of terms to look up.

list
	A	B	C	D	E	F	G	H	I	J
1	Code found?	Code found? Spill to maxrows	Example spill			Mixed codes		Match String		Maxrows
2	YES	Y	$F$2			sdaHello1dsfsd		Hello1		10
3	NO	N	$F$3			34f43t4		Hello2
4	NO	N	$F$4			54y54y5		3Bye
5	YES	Y	$F$5			dsfsHello1mbmbmb		2Bye
6	YES	Y	$F$6			etjrjyrjty2Bye
7	NO	Y	$F$7			sjfdgnjs
8	NO	Y	$F$8			shthmrhkBye
9	NO	Y	$F$9			safreHello
10	YES	Y	$F$10			dsggmkmkm 2Bye 1111
Sheet2

Cell Formulas
Range	Formula
B2:B10	B2	=LET(Drng,TRANSPOSE($H$1:$H$20),Rng,F2:INDEX(F:F,Maxrows),IF(MIN(LEN(SUBSTITUTE(Rng,Drng,"")))<LEN(Rng),"Y","N"))
C2:C10	C2	=ADDRESS(ROW(F2:INDEX(F:F,Maxrows))-ROW(F2)+2,6)
A2:A10	A2	=IF(SUM(($H$2:$H$10<>"")*(ISNUMBER(MATCH("*"&$H$2:$H$10&"*",INDEX(F:F,ROW()),0)))), "YES", "NO")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Maxrows	=Sheet2!$J$2	B2:C2