Generate All Permutations in Excel using LAMBDA

Cool question and brain teezer; I just puzzled on something which is using MAKEARRAY(): Option 1: What you’d call as “super inefficient” is creating a list of permutations when you calculate rows^columns. I think the following is not that inefficient. Let’s imagine the following: Formula in E1: =LET(A,A1:C3,B,ROWS(A),C,COLUMNS(A),D,B^C,E,UNIQUE(MAKEARRAY(D,C,LAMBDA(rw,cl,INDEX(IF(A=””,””,A),MOD(CEILING(rw/(D/(B^cl)),1)-1,B)+1,cl)))),FILTER(E,MMULT(–(E<>””),SEQUENCE(C,,,0))=C)) In short, what this does: Variables A-D … Read more

CountIf With Filtered Data

I was able to determine that: SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)) is used to return an array of which cells are visible and hidden in the range. 1 is returned for visible and 0 is returned for hidden. ISNUMBER(SEARCH(“Pear”,B2:B18))+0) is used to return an array of which cells contain “Pear”. If “Pear” is found, 1 is returned, else 0. … Read more

Three Dimensional Lookup Using INDEX/MATCH

I used an IF() statement array formula to find what the P row number was after the George row… I also needed to use the MIN() function to get the first P row number after the name. Beyond that, it’s a simple INDEX() function…. that racked my brain for over an hour :). =INDEX($A$1:$D$9,MIN(IF((ROW(A1:A9)>MATCH($F$4,A1:A9,0))*(A1:A9=$F$5),ROW(A1:A9),””)),MATCH($F$6,$A$1:$D$1,0)) Don’t … Read more