LET formula to spill dynamically

MurdochQuill

Board Regular
Joined
Nov 21, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have another question regarding =LET.

I currently use this let formula
Excel 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

Well-known Member
Joined
Dec 30, 2008
Messages
13,789
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

MurdochQuill

Board Regular
Joined
Nov 21, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 16, 2009
Messages
289
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,789
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1Code found?Code found? Spill to maxrowsMixed codesMatch StringFound?Maxrows
2YESYsdaHello1dsfsdHello1Y10
3NON34f43t4Hello2NMatchRows
4NON54y54y53ByeN5
5YESYdsfsHello1mbmbmb2ByeY
6YESYetjrjyrjty2Bye
7NONsjfdgnjs
8NOYshthmrhkBye
9NOYsafreHello
10YESYdsggmkmkm 2Bye 1111
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=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:F5F2=IF(ISNUMBER(XMATCH("*"&E2:INDEX(E:E,MatchRows)&"*",C2:INDEX(C:C,MaxRows),2)),"Y","N")
H4H4=MATCH("zzz",E:E)
A2:A10A2=IF(SUM(($E$2:$E$10<>"")*(ISNUMBER(MATCH("*"&$E$2:$E$10&"*",INDEX(C:C,ROW()),0)))), "YES", "NO")
Dynamic array formulas.
 

Forum statistics

Threads
1,143,677
Messages
5,720,246
Members
422,272
Latest member
ginkgoVil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top