{"id":52685,"date":"2025-03-12T14:00:00","date_gmt":"2025-03-12T13:00:00","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=52685"},"modified":"2025-06-03T22:07:07","modified_gmt":"2025-06-03T20:07:07","slug":"criacao-de-resultados-no-google-sheets-com-mapa-e-lambda","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/pt\/blog\/google-sheets-formulas\/crafting-dresults-in-google-sheets-with-map-and-lambda\/","title":{"rendered":"Cria\u00e7\u00e3o de resultados 2D din\u00e2micos no Planilhas Google com as fun\u00e7\u00f5es MAP e LAMBDA"},"content":{"rendered":"\n[et_pb_section fb_built=”1″ theme_builder_area=”post_content” _builder_version=”4.27.2″ _module_preset=”default”][et_pb_row _builder_version=”4.27.2″ _module_preset=”default” theme_builder_area=”post_content”][et_pb_column _builder_version=”4.27.2″ _module_preset=”default” type=”4_4″ theme_builder_area=”post_content”][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” theme_builder_area=”post_content” hover_enabled=”0″ sticky_enabled=”0″]
\n

Note:<\/strong> This post was originally published in our community forum.<\/p>\n<\/blockquote>\n

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\u2019ll explore how to leverage these functions using a practical example from a product tracking scenario.<\/p>\n

Scenario Overview<\/h2>\n

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.<\/p>\n

Data Structure<\/h2>\n

Our main dataset, named `Product`, is formatted as follows:<\/p>\n

\"\"<\/a><\/p>\n

Objective<\/h2>\n

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\u2019s added to the `Product` tab.<\/p>\n

Step-by-Step Implementation<\/h2>\n

1. Setting Up Date Headers<\/h3>\n

First, we generate the headers for each month in the summary table:<\/p>\n

Cell B1:<\/strong><\/p>\n

=<\/span>ARRAYFORMULA<\/span>(IF<\/span>($B<\/span>$2<\/span>:$2<\/span>=<\/span>\"\"<\/span>, \"\"<\/span>, TEXT<\/span>($B<\/span>$2<\/span>:$2<\/span>,\"yy\"<\/span>)&<\/span>\"-\"<\/span>&<\/span>TEXT<\/span>(MONTH<\/span>($B<\/span>$2<\/span>:$2<\/span>),\"00\"<\/span>)))<\/code><\/pre>\n
This formula uses `ARRAYFORMULA` to apply text formatting across each date in row 2, creating a YY-MM format for month headers.<\/p>\n
2. Generating Monthly Date Ranges<\/h3>\n
Next, we prepare a list of end-of-month dates for each month within our desired range:<\/p>\n
Cell C2:<\/strong><\/p>\n
=<\/span>ARRAYFORMULA<\/span>(EOMONTH<\/span>($B<\/span>$2<\/span>,SEQUENCE<\/span>(1<\/span>,15<\/span>+<\/span>12<\/span>,1<\/span>,1<\/span>)))<\/code><\/pre>\n
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).<\/p>\n
3. Listing Unique Products<\/h3>\n
We identify all unique products from the `Product` tab:<\/p>\n
Cell A4:<\/strong><\/p>\n
=<\/span>UNIQUE<\/span>(Product<\/span>!$B<\/span>$2<\/span>:$B<\/span>)<\/code><\/pre>\n
4. Dynamic Price Mapping<\/h3>\n
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:<\/p>\n
Cell B4:<\/strong><\/p>\n
=<\/span>MAP<\/span>($A<\/span>$4<\/span>:$A<\/span>, LAMBDA<\/span>(product,\n  IF<\/span>(product=<\/span>\"\"<\/span>,\"\"<\/span>, \n    MAP<\/span>($B<\/span>$1<\/span>:$1<\/span>, LAMBDA<\/span>(month, \n      IF<\/span>(month=<\/span>\"\"<\/span>, \"\"<\/span>, IFNA<\/span>(QUERY<\/span>(FILTER<\/span>(Product<\/span>!$B<\/span>$2<\/span>:$D<\/span>, TEXT<\/span>(Product<\/span>!$D<\/span>$2<\/span>:$D<\/span>,\"YY-MM\"<\/span>)=<\/span>month, Product<\/span>!$B<\/span>$2<\/span>:$B<\/span>=<\/span>product),\"SELECT Col2 ORDER BY Col3 DESC LIMIT 1\"<\/span>),)\n      )\n    ))\n  )\n))<\/code><\/pre>\n
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`).<\/p>\n
Conclusion<\/h2>\n
By integrating `MAP` and `LAMBDA` with other Google Sheets functions, we\u2019ve 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.<\/p>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n","protected":false},"excerpt":{"rendered":"
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\u2019ll explore how […]<\/p>\n","protected":false},"author":49,"featured_media":44470,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[],"class_list":["post-52685","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/52685","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/49"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=52685"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/52685\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/44470"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=52685"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=52685"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=52685"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n