How to use the powerful MAP Function in Google Sheets
ฝัง
- เผยแพร่เมื่อ 5 ก.พ. 2024
- The MAP function in Google Sheets is a powerful function for working with ranges (arrays) of data.
It takes array(s) of data as an input and "maps" each value to a new value based on a custom LAMBDA function.
MAP is a more modern, functional approach to array formula type problems.
📚 Additional resources:
- Template to follow along: docs.google.com/spreadsheets/...
- MAP Function written tutorial: www.benlcollins.com/spreadshe...
- LAMBDA Functions course: courses.benlcollins.com/p/lam...
- Formula syntax differences for European users: www.benlcollins.com/spreadshe...
✉️ Google Sheets Tips Newsletter, my free weekly newsletter:
www.benlcollins.com/google-sh...
🎓 Join 68,000+ professionals in my courses: courses.benlcollins.com/
#googlesheets #googlesheetstutorial #spreadsheet - วิทยาศาสตร์และเทคโนโลยี
Really an excellent method of teaching with several examples and showing the original method plus the MAP method. Lots of nuggets in between as well.
Thanks, Scott! 🙏
Fantastic explanation thank you
Great thanks, now everything is clear🙏🙏
Not related to this video,; at my new job I am relegated to using the old ball-and-chain, bloatware behemoth spreadsheet (which I refer to as _Decel_ because it grinds my productivity to a halt). I dropped it as I would a flaming turd many years ago when Google sheets hit its stride and never looked back. Now I'm faced with daily reminders of why I left it behind. If using Google sheets is likened to zipping about on an electric scooter bike then using Decel is like trying to jog in ski boots and a weighted vest.
I come home and watch your tutorials for comfort.
The MMULT installment is straight fire.
Good luck at your new job!
@@benlcollins Ha, thanks.
Thanks for the great explanation of the MAP function! I did find that using TOCOL and TOROW allows you to work with arrays of different orientations/shapes such as the example you gave around 4:50.
Thanks, Levi. Yes, TOCOL and TOROW and both great functions too.
This is an excellent demonstration of what can be done with the MAP function, though I think it is worthwhile to add a discussion about the differences between it and ARRAYFORMULA and what can be achieved by either.
As far as the initial example of the x*2 case (1:28), the same results can be achieved with an ARRAYFORMULA(array*2) function, through a much shorter syntax (as you’ve acknowledged in 9:35). However, the “x of y” example (7:44) is an excellent testcase to show the differences in the inner logics of the two.
In your example, you’ve referenced the original cell (A3) as both the source for the SEQUENCE value as well as the “tail-out” applied through the LAMBDA function, concatenating it after el&“ of ”.
Suppose we’d like to use the SEQUENCE itself as the reference for the tail-out. Using the ARRAYFORMULA syntax, this can be achieved with the following:
= ArrayFormula(Sequence(A3) & " of " & Max(Sequence(A3)))
Or, to use a similar logic as to what’s going on with a LAMBDA function, one could even utilize the same instance of the SEQUENCE function by using LET as follows:
= Let(el,Sequence(A3),ArrayFormula(el & " of " & Max(el)))
However, if we’ll adjust the MAP example you’ve used accordingly -
= Map(Sequence(A3),Lambda(el,el & " of " & Max(el)))
The result will be different than expected: instead of getting “x of y”, where x iterates through the array and y stays constant - we’ll be getting an “x of x” result, where x still iterates but seemingly with no constant y.
This difference in behaviour seems to me as crucial to understand and make proper decisions as to when to implement the MAP function vs. ARRAYFORMULA.
ARRAYFORMULA allows us to apply values from an array to be mass-processed by _non-array functions._ This is done through using _the array as a single instance,_ and is limited as to how it behaves with functions that are _designed to work with arrays_ to begin with - for example, try using ARRAYFORMULA with the TEXTJOIN or CONCATENATE functions and it’ll either issue an error or give unexpected results.
MAP, however, takes an array and _iterates its values_ as it passes them along to the LAMBDA function, essentially making a separate dedicated input for each instance to be calculated. Thus, one can iterate the original array also in a manner which can be used with array functions, as only the limited, relevant set of values needed for the specific iterated output to be calculated will be passed.
Back to the “x of y” example - when using ARRAYFORMULA, the result is being calculated with the array input _as a whole_ - so the MAX() command always has the full sequence array as defined through A3 to consider. But once MAP is used to process the sequence, each result has _only the single value of its dedicated iteration;_ essentially, it iterates over multiple instances of arrays each holding a single value. For the 5th result, for example, it deals with an input “array” of the single value of “5” - hence “5” is also the array’s MAX value, and we’ll get “5 of 5” before moving to the next iteration - a single-cell array with the value of “6”, resulting in the output “6 of 6”.
Therefore, one of the initial things to consider in judging whether a certain task is more fitted for a MAP or for ARRAYFORMULA - and separately from the issue of whether the functions applied are compatible with ARRAYFORMULA usage to begin with - is whether the intentions are to apply a process to _an array as a whole_ - meaning, as a complete _set_ of values - or whether the array is being used as a method to group (or filter) the values to be processed, with the actual aim for carrying out a process on each “record” _within_ the set, but rather independently of the set as a whole.
Today I learnt.... MAP, LAMBDA, SEQUENCE. But I just saw the BYROW and BYCOL listed which I think all of these will address some of the limitations I have had with ARRAYFORMULA, QUERY and the PIVOT in QUERY where I want to sort both dimensions by an aggregated value. Love your work mate! Pretty sure my friends and colleagues are getting sick of me sending links to your stuff telling them how to do their job better :D
Oh wait... just found the link to your 10 days of LAMBDA functions course in the description. This changes everything
Thanks, Josh! These functions are a real game-changer 💪
I'm trying to use map lambda with sparkline since sparkline can't use arrays, it's quite tricky.
Hey Ben Collins!? (aka King of Functions) When will we see you back on Totally Unscripted? 😬 Great work mate!
Sure, that'd be fun!
again perfect explanation by Ben,.... only ....apparently one's beard grows *very* fast when working on Sheets ;-)
Haha! When I need to shorten it, I just use the REDUCE lambda function...
Hello Sir, is this work with importrange formula from another spreadsheet file?
Great tutorial. It definitely helped me to understand how to use the new function.
I do have to say that i am not sure i see the benefit of using this function as opposed to an array formula. Am i missing something?
Great question! There are two advantages to lambdas I see:
1) you can use functions like INDEX or logical operators AND, OR with arrays now, which you can't do with the ArrayFormula
2) I think the lambda formulas are easier to write/understand once you get the hang of them, especially for complex formulas.
But of course, nothing wrong with using array formulas and I haven't tested speed comparisons at large scales.
@@benlcollins Thank you for the tips I will have to try it using the index, and, or functions.
Keep up the great work!
🙏👍