Using an Excel Formula to Filldown

[MurdochQuill]

Hi all,

I'm looking to filldown my formula using a formula (if that's possible). If anyone has knowledge on how to do this it would be really helpful.

Here's the example formula I'm looking to fill down using only in cell formula. Formula works alright as is.. but instead of having it bound to a strict range, I would like it to move dynamically.
I have a global called "Maxrows" which returns the dynamic amount of rows it should be applied to.

=IFERROR(IF(XLOOKUP(INT(MID(INDEX(G:G,ROW()),SEARCH(", ",INDEX(G:G,ROW()))+5,LEN(INDEX(G:G,ROW()))-SEARCH(", ",INDEX(G:G,ROW()))-10)), K:K,L:L,"",0)=0,"",XLOOKUP(INT(MID(INDEX(G:G,ROW()),SEARCH(", ",INDEX(G:G,ROW()))+5,LEN(INDEX(G:G,ROW()))-SEARCH(", ",INDEX(G:G,ROW()))-10)), K:K,L:L,"",0)),"")

 

[jasonb75]

Data sample and explanation so that we don't have to (perhaps incorrectly) reverse engineer the existing formula?

You could try replacing INDEX(G:G,ROW()) with G$2:INDEX(G:G,Maxrows)) which may or may not allow it to spill as a dynamic array. That is the best I can do without more information and something to test on.

 

[jasonb75]

I've tried to pick your formula apart, see if this works

=LET(r,G2:INDEX(G:G,MATCH("zzz",G:G)),IFERROR(1/(1/XLOOKUP(INT(MID(r,SEARCH(", ",r)+5,5)),K:K,L:L,"",0)),""))

 

[MurdochQuill]

Thanks for the attempt.
Yes it's quite a long and convoluted formula.

What it achieves is this: Col F has data in it like the following, which is random but contains important data after ", ".

sdfhgfjhghfdj3234253345sgfh, 9999999. y

It pulls the "9999999" from the row the formula is on. It then matches the pulled number with Col M on the same row. If the data is a match, then return value in Col N on the same row.
Here's the original for clarification:

=IFERROR(IF(XLOOKUP(INT(MID(INDEX(F:F,ROW()),SEARCH(", ",INDEX(F:F,ROW()))+2,LEN(INDEX(F:F,ROW()))-SEARCH(", ",INDEX(F:F,ROW()))-4)), M:M,N:N,"",0)=0,"",XLOOKUP(INT(MID(INDEX(F:F,ROW()),SEARCH(", ",INDEX(F:F,ROW()))+2,LEN(INDEX(F:F,ROW()))-SEARCH(", ",INDEX(F:F,ROW()))-4)), M:M,N:N,"",0)),"")

 

[Fluff]

How about

=LET(Rng,F2:INDEX(F:F,MaxRows),Start,FIND(", ",Rng),Return,XLOOKUP(INT(MID(Rng,Start+2,LEN(Rng)-Start-4)),M:M,O:O,"",0),IF(Return=0,"",Return))

 

[MurdochQuill]

How about

=LET(Rng,F2:INDEX(F:F,MaxRows),Start,FIND(", ",Rng),Return,XLOOKUP(INT(MID(Rng,Start+2,LEN(Rng)-Start-4)),M:M,O:O,"",0),IF(Return=0,"",Return))

Hi Fluff,

That's a really nice formula. Do you have any comprehensive sources on learning ways to use the =LET formula?

 

[Fluff]

Glad we could help & thanks for the feedback.

Do you have any comprehensive sources on learning ways to use the =LET formula?

Not really, but if you search the net I'm sure there will be plenty of sites giving some detail.