No video

How to use the powerful MAP Function in Google Sheets

  Рет қаралды 5,579

Ben Collins

Ben Collins

Күн бұрын

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.co...
- MAP Function written tutorial: www.benlcollin...
- LAMBDA Functions course: courses.benlco...
- Formula syntax differences for European users: www.benlcollin...
✉️ Google Sheets Tips Newsletter, my free weekly newsletter:
www.benlcollin...
🎓 Join 68,000+ professionals in my courses: courses.benlco...
#googlesheets #googlesheetstutorial #spreadsheet

Пікірлер: 25
@Scott-sm9nm
@Scott-sm9nm 6 ай бұрын
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.
@benlcollins
@benlcollins 6 ай бұрын
Thanks, Scott! 🙏
@jozsefolasz8702
@jozsefolasz8702 28 күн бұрын
It's interesting, the best the content, the lowest the number of the subscribers. Your sparkline hour was priceless :)
@benlcollins
@benlcollins 28 күн бұрын
Thanks! 🙏
@helmanfrow
@helmanfrow 6 ай бұрын
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.
@benlcollins
@benlcollins 6 ай бұрын
Good luck at your new job!
@helmanfrow
@helmanfrow 6 ай бұрын
@@benlcollins Ha, thanks.
@JoshPeak
@JoshPeak 6 ай бұрын
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
@JoshPeak
@JoshPeak 6 ай бұрын
Oh wait... just found the link to your 10 days of LAMBDA functions course in the description. This changes everything
@benlcollins
@benlcollins 6 ай бұрын
Thanks, Josh! These functions are a real game-changer 💪
@Ofer.Sheinberg
@Ofer.Sheinberg 4 ай бұрын
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.
@levimeyer1591
@levimeyer1591 6 ай бұрын
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.
@benlcollins
@benlcollins 6 ай бұрын
Thanks, Levi. Yes, TOCOL and TOROW and both great functions too.
@mikolaskage
@mikolaskage 4 ай бұрын
Great thanks, now everything is clear🙏🙏
@user-zw8sp7ds2k
@user-zw8sp7ds2k 5 ай бұрын
Fantastic explanation thank you
@TotallyUnscriptedShow
@TotallyUnscriptedShow 6 ай бұрын
Hey Ben Collins!? (aka King of Functions) When will we see you back on Totally Unscripted? 😬 Great work mate!
@benlcollins
@benlcollins 6 ай бұрын
Sure, that'd be fun!
@herilagan6666
@herilagan6666 2 ай бұрын
Hello Sir, is this work with importrange formula from another spreadsheet file?
@Sentinaut
@Sentinaut 2 ай бұрын
I'm trying to use map lambda with sparkline since sparkline can't use arrays, it's quite tricky.
@coreymobrien
@coreymobrien 6 ай бұрын
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?
@benlcollins
@benlcollins 6 ай бұрын
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.
@coreymobrien
@coreymobrien 6 ай бұрын
@@benlcollins Thank you for the tips I will have to try it using the index, and, or functions. Keep up the great work!
@Netboosters
@Netboosters 6 ай бұрын
again perfect explanation by Ben,.... only ....apparently one's beard grows *very* fast when working on Sheets ;-)
@benlcollins
@benlcollins 6 ай бұрын
Haha! When I need to shorten it, I just use the REDUCE lambda function...
@zaccoza
@zaccoza 6 ай бұрын
🙏👍
How to Use XLOOKUP in Google Sheets
15:01
Ben Collins
Рет қаралды 3,5 М.
Excel REDUCE Function - LAMBDA Array Formulas in Excel & Google Sheets
12:32
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 16 М.
艾莎撒娇得到王子的原谅#艾莎
00:24
在逃的公主
Рет қаралды 54 МЛН
Can This Bubble Save My Life? 😱
00:55
Topper Guild
Рет қаралды 87 МЛН
Секрет фокусника! #shorts
00:15
Роман Magic
Рет қаралды 45 МЛН
Or is Harriet Quinn good? #cosplay#joker #Harriet Quinn
00:20
佐助与鸣人
Рет қаралды 14 МЛН
QUERY Complete guide: Google Sheets' most complex function
18:06
David Benaim
Рет қаралды 44 М.
Google Sheets Query Function Explained
12:35
Leila Gharani
Рет қаралды 303 М.
ARRAYFORMULA in Google Sheets - 4 useful hacks included 🎁
16:31
Coupler․io Academy
Рет қаралды 90 М.
The Ultimate Guide to Creating Lists in Google Sheets
21:23
Ben Collins
Рет қаралды 2,9 М.
Excel LAMBDA Function
13:54
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 16 М.
Google sheets scan function-very powerful function. six examples
24:39
TopTech KIT (Tech simplified)
Рет қаралды 640
Smart Chips in Google Sheets 🧠
13:38
Ben Collins
Рет қаралды 7 М.
Running Total, Array - Excel & Google Sheets, SCAN, REDUCE, MAP
18:45
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 17 М.
艾莎撒娇得到王子的原谅#艾莎
00:24
在逃的公主
Рет қаралды 54 МЛН