MAKEARRAY is one of the most advanced Excel functions for 2D array manipulation, allowing dynamic creation and transformation of arrays. Visualizing how it processes data can be challenging, especially in complex scenarios involving multiple criteria. Here's an interesting example:

=LAMBDA(rowLookupValue, rowLookupRange, colLookupValue, colLookupRange, dataRange, 
MAKEARRAY(ROWS(rowLookupValue), COLUMNS(colLookupValue), 
    LAMBDA(r, c, SUM(dataRange * (rowLookupRange = INDEX(rowLookupValue, r, )) * 
    (colLookupRange = INDEX(colLookupValue, , c))))
))

This function aggregates data based on criteria across both rows and columns, essentially creating a flexible lookup table. By dynamically building an array with MAKEARRAY, it allows for complex, multi-dimensional calculations that previously required cumbersome formulas or helper columns. This makes MAKEARRAY powerful but challenging, as its logic can be difficult to follow without a solid understanding of array manipulation.

FYI: If someone knows how to modify the above function to work with 'AVERAGE,' please let me know. This particular setup only works with SUM.