Need help explaining this formula provided to me

Question

I recently posted on here to get help with a formula, here is the link...https://stackoverflow.com/questions/75068029/vlook-up-style-forumla-but-range-is-2-cells A user called rockinfreakshow was really awesome and provided a great solution for me. I'm not very experienced and don't understand what the formula at all but I'd love to be able to add more attributes to it. Is anyone able to help break it down for me ?

I havent tried anything here, it's totally out of my realm of understanding

=MAKEARRAY(COUNTA(B2:B),COUNTA(D1:O1),LAMBDA(r,c,IF(REGEXMATCH(LAMBDA(ax,bx,IFS(REGEXMATCH(ax,"Mixed")*REGEXMATCH(INDEX(C2:C,r),"Blend")*REGEXMATCH(INDEX(C2:C,r),"Filter"),"BLEND-"&bx&"|FILTER-"&bx,REGEXMATCH(ax,"Mixed")*NOT(REGEXMATCH(INDEX(C2:C,r),"Blend"))*REGEXMATCH(INDEX(C2:C,r),"Filter"),"ESP-"&bx&"|FILTER-"&bx,REGEXMATCH(ax,"Mixed")*NOT(REGEXMATCH(INDEX(C2:C,r),"Filter")),"BLEND-"&bx&"|ESP-"&bx,LEN(ax),SUBSTITUTE(ax&"-"&bx,"Espresso","ESP")))(regexextract(INDEX(B2:B,r),"([^\s]*?) Subscription"),IFNA(SWITCH(REGEXEXTRACT(INDEX(C2:C,r),"Small|Medium|Large"),"Small",250,"Medium",450,"Large",900),SWITCH(REGEXEXTRACT(INDEX(B2:B,r),"Medium|Large"),"Medium",225,"Large",450))),"(?i)"&INDEX(D1:O1,,c)),1,)))

@player0 Well I just want to essentially add in a new "product" so see where there's the Filter, Blend and Espresso text strings being pulled out? Id like to just do the with "DECAF" into a new set of coloumns and then also there'd be one "FOMO" and its puts a "1" in each of the "225" columns — Michael Tyson, Feb 03 '23 at 01:50
btw, the best practice is to ask the author of the formula for an explanation right on that post before accepting the answer — player0, Feb 03 '23 at 12:48

player0 · Answer 1 · 2023-02-03T02:45:34.483

see the WHY LAMBDA? part of this answer to understand the LAMBDA

the formula contains 2x LAMBDA and there are a total of 4 placeholders which translates to:

r   - COUNTA(B2:B)
c   - COUNTA(D1:O1)
ax  - REGEXEXTRACT(INDEX(B2:B, r), "([^\s]*?) Subscription")
bx  - IFNA(SWITCH(REGEXEXTRACT(INDEX(C2:C, r), "Small|Medium|Large"), 
      "Small", 250, "Medium", 450, "Large", 900), 
      SWITCH(REGEXEXTRACT(INDEX(B2:B, r), "Medium|Large"), 
      "Medium", 225, "Large", 450))

r counts how many items are in B column
c counts how many items are in row 1 of range D1:O1
ax extracts the word from B column that precedes the word Subscription
bx is a bit complex but essentially it extracts from C column word Small or Medium or Large and replaces it with 250, 450 or 900 respectively. then if C column does not contain one of those 3 words it checks for Medium or Large within B column and assigns 225 or 450 respectively

what we are left with is the core of the formula:

IFS(    REGEXMATCH(ax,             "Mixed")*
        REGEXMATCH(INDEX(C2:C, r), "Blend")*
        REGEXMATCH(INDEX(C2:C, r), "Filter"),  "BLEND-"&bx&"|FILTER-"&bx, 
___________________________________________________________________________

        REGEXMATCH(ax,             "Mixed")*
    NOT(REGEXMATCH(INDEX(C2:C, r), "Blend"))*
        REGEXMATCH(INDEX(C2:C, r), "Filter"),  "ESP-"&bx&"|FILTER-"&bx, 
___________________________________________________________________________

        REGEXMATCH(ax,             "Mixed")*
    NOT(REGEXMATCH(INDEX(C2:C, r), "Filter")), "BLEND-"&bx&"|ESP-"&bx, 
___________________________________________________________________________

    LEN(ax), SUBSTITUTE(ax&"-"&bx, "Espresso", "ESP"))

for better visualization, the IFS formula contains only 4 elements. each of these 4 elements acts as a switch - if there is a match x we get output y. for example let's dissect the first element...

REGEXMATCH(ax,             "Mixed")*
REGEXMATCH(INDEX(C2:C, r), "Blend")*
REGEXMATCH(INDEX(C2:C, r), "Filter"),  "BLEND-"&bx&"|FILTER-"&bx

there are 3x REGEXMATCHes multiplied by each other. whenever there is such multiplication in array formulae it translates as AND logic gate (if there would be + it would mean OR logic gate) eg.:

1 * 1 = 1
1 * 0 = 0
0 * 1 = 0
0 * 0 = 0

REGEXMATCH outputs TRUE or FALSE so if we get 3x TRUE the whole argument is considered as TRUE (because 1 * 1 * 1 = 1) so we proceed to output our first switch

therefore if B column contains Mixed and C column contains Blend and C column contains Filter then we output Blend-000|Filter-000 where 000 stands for a specific number determined from bx placeholder/formula and also you can notice the | (which btw stands for OR logic within the regex) but in this case, it's just a unique symbol to join stuff for REGEXMATCH. which REGEXMATCH is this for you may ask? ...this one:

so the output of IFS formula is the input for most outer REGEXMATCH and we check if the IFS output matches something within D1:O1 range. IF yes then output 1 otherwise output nothing. shortened:

IF(REGEXMATCH(IFS(...), "(?i)"&INDEX(D1:O1,,c), 1, )

(?i) in regex means "case insensitive". it is there just for safety reasons because regex is by default case sensitive.

and we reached the MAKEARRAY formula that creates an array of numbers across the whole range with height r and width c where output is the result of IF eg. either 1 or empty cell

Need help explaining this formula provided to me

1 Answers1