Crafting dynamic 2D results in Google Sheets with MAP and LAMBDA functions

Note: This post was originally published in our community forum.

Google Sheets offers a robust set of features that allow users to handle data dynamically and efficiently. With the addition of LAMBDA and MAP functions, you can simplify complex operations into single-cell formulas that dynamically generate two-dimensional (2D) results. In this blog, we’ll explore how to leverage these functions using a practical example from a product tracking scenario.

Scenario Overview

Imagine you are tracking the prices of various products over several months. Your dataset records product details and price updates at different times. The challenge is to summarize the monthly price updates for each product into a single summary table, displaying the latest price of each product by the end of each month.

Data Structure

Our main dataset, named `Product`, is formatted as follows:

Objective

The objective is to create a `Monthly Product Summary` table that dynamically updates to show the latest price of each product by the end of each month. This table should adapt to new data as it’s added to the `Product` tab.

Step-by-Step Implementation

1. Setting Up Date Headers

First, we generate the headers for each month in the summary table:

Cell B1:

=ARRAYFORMULA(IF($B$2:$2="", "", TEXT($B$2:$2,"yy")&"-"&TEXT(MONTH($B$2:$2),"00")))

This formula uses `ARRAYFORMULA` to apply text formatting across each date in row 2, creating a YY-MM format for month headers.

2. Generating Monthly Date Ranges

Next, we prepare a list of end-of-month dates for each month within our desired range:

Cell C2:

=ARRAYFORMULA(EOMONTH($B$2,SEQUENCE(1,15+12,1,1)))

Here, `EOMONTH` and `SEQUENCE` generate end-of-month dates starting from the date in `B2`, covering a range of potentially 27 months (15 + 12 as an example).

3. Listing Unique Products

We identify all unique products from the `Product` tab:

Cell A4:

=UNIQUE(Product!$B$2:$B)

4. Dynamic Price Mapping

The most complex part is creating a dynamic summary of prices. This involves mapping over the list of products and, for each product, mapping over the list of months to fetch the latest price by the end of each month:

Cell B4:

=MAP($A$4:$A, LAMBDA(product,
  IF(product="","", 
    MAP($B$1:$1, LAMBDA(month, 
      IF(month="", "", IFNA(QUERY(FILTER(Product!$B$2:$D, TEXT(Product!$D$2:$D,"YY-MM")=month, Product!$B$2:$B=product),"SELECT Col2 ORDER BY Col3 DESC LIMIT 1"),)
      )
    ))
  )
))

This formula maps over each product, then maps over each month. It uses `FILTER` to narrow down records to the specific product and month, then `QUERY` to fetch the latest price for that month using the timestamp (`ORDER BY Col3 DESC LIMIT 1`).

Conclusion

By integrating `MAP` and `LAMBDA` with other Google Sheets functions, we’ve created a dynamic, easily updatable summary table that adapts as new data is added. This approach minimizes the need for manual updates and allows for extensive scalability in managing product data over time. This is just one example of how powerful these functions can be in transforming your data handling and analysis in Google Sheets.