Thank you for mentioning me !! 🙏 Also your formula index (x)match (x)match spills (is spill-erific 😀) =INDEX(H4:L8,XMATCH(C4:C40,G4:G8),XMATCH(B4:B40,H3:L3))*A4:A40 or =INDEX(H4:L8,XMATCH(C4:C40,G4:G8),XMATCH(B4:B40,G4:G8))*A4:A40 since (x)match is 1 dimension , in this case, H3:L3 G4:G8 ✌
Comments are not being posted by RUclips. There is a terrible bug that RUclips has not fixed. Here is comment from from Bill Szysz: Beautiful!!! I figured that now we have LAMDA function so, maybe let's not stop halfway. Maybe let's build functions for Excel novices who don't know anything about VLOOKUP, XLOOKUP, INDEX and MATCH. :-))) Function "GetTargetAmount" definition (Name manager): =LAMBDA(rng,currTbl,BYROW(rng,LAMBDA(row,VLOOKUP(INDEX(row,3),currTbl,XMATCH(INDEX(row,2),INDEX(currTbl,1,)),0)*INDEX(row,1)))) where rng is our data range (A4:C40) and currTbl is our currency table with header and with the most left column (G3:L8). Now we can use it in D3 -> GetTargetAmount(A4:C40, G3:L8) Voila :-))))
VLOOKUP arose in the new world of spilled arrays and cried "where's MY hash!?!" Young MAP said "I'm still in training but I will try to serve it right up". And Sweet LAMBDA said "Don't worry old VL. As soon as MAP gets it ready I will bring it right to you cuz EXCEL isn't done with you just yet!!!
This vdo is excellent as always, here I tested F9 evaluation key by recreating this, as F9 evaluation key having other issues such as #Name error, #NA, output incorrect or result incorrect when pressing F9 evaluation. All is send to MS, and hopefully, it will be resolved and updated in their upcoming builds / updates.
I wrote to MS regarding F9 evaluation key bug inside LET function, hopefully it will be resolved. Keep following it up. In addition to that I wrote user ID, pwd in Name Manager, so other user can't see inside of the formula by accessing name manager. Lastly pdf import functionality in Excel is very basic, like if we would like to pull certain things from invoice, say date, invoice #, amount...we can't target that data specifically. Looking fwd to have their resolutions from MS development team.
Hello Mike. In my organisation we are using office 365 but still Lamda function is not available. Do you have any ideas why it is not available?? Thank you in advance
LET is for when you have repetitive formula elements and want to define a variable and evaluate variables one time only and save results for rest of formula. LAMBDA is for defining reusable functions or for creating function values that you can use in related functions like MAP and BYROW.
Thank you for mentioning me !! 🙏 Also your formula index (x)match (x)match spills (is spill-erific 😀)
=INDEX(H4:L8,XMATCH(C4:C40,G4:G8),XMATCH(B4:B40,H3:L3))*A4:A40
or
=INDEX(H4:L8,XMATCH(C4:C40,G4:G8),XMATCH(B4:B40,G4:G8))*A4:A40
since (x)match is 1 dimension , in this case, H3:L3 G4:G8 ✌
That is 100 times easier. Thanks for the bonus on the bonus : )
I added this formula to the download workbook - for the Team!!!!!
Comments are not being posted by RUclips. There is a terrible bug that RUclips has not fixed. Here is comment from from Bill Szysz:
Beautiful!!!
I figured that now we have LAMDA function so, maybe let's not stop halfway.
Maybe let's build functions for Excel novices who don't know anything about VLOOKUP, XLOOKUP, INDEX and MATCH. :-)))
Function "GetTargetAmount" definition (Name manager):
=LAMBDA(rng,currTbl,BYROW(rng,LAMBDA(row,VLOOKUP(INDEX(row,3),currTbl,XMATCH(INDEX(row,2),INDEX(currTbl,1,)),0)*INDEX(row,1))))
where rng is our data range (A4:C40) and currTbl is our currency table with header and with the most left column (G3:L8).
Now we can use it in D3 -> GetTargetAmount(A4:C40, G3:L8)
Voila :-))))
Thanks Mike. Simply Amazing!!!!
It is sorta cool ; ) Lucky we have a great Team!!!!!
I say bring on MAP to the masses. Thanks Mike
Yes, MAP to the masses!!!!!!!! Go Team!!!!!!
VLOOKUP arose in the new world of spilled arrays and cried "where's MY hash!?!" Young MAP said "I'm still in training but I will try to serve it right up". And Sweet LAMBDA said "Don't worry old VL. As soon as MAP gets it ready I will bring it right to you cuz EXCEL isn't done with you just yet!!!
That is the BEST!!!!!!! You are a poet with words. I LOVE what you wrote!!!!!!
haha amazing storytelling right there ! 😝👌👌
Wow. EXCELlent. Go Team.
Go Team!
Wow that was awesome Mike! Great video
Glad you like it, Chris M !!!!! So lucky to have such a great Team : )
Super Brilliant Map & Lamda
Exceλambda INDEX formula below is even better : )
Wauw, next level formula!
It is, but Check out the next video too (easier): ruclips.net/video/xbuUcG8pZQg/видео.html
This vdo is excellent as always, here I tested F9 evaluation key by recreating this, as F9 evaluation key having other issues such as #Name error, #NA, output incorrect or result incorrect when pressing F9 evaluation. All is send to MS, and hopefully, it will be resolved and updated in their upcoming builds / updates.
Thanks for helping all of us by submitting good feedback to Microsoft! Go Team!!!!
That's Amazing Mike ... thanks alot.
Go Team!
This video: dedicated to ALL those "v-lookups haters" 😝😝😝 amazing Mike !!! 👌👌👌👌 #GoTEAM 🏆🏆
VLOOKUP and the original LOOKUP still have great uses!!!!!!!! Go Team!!!!!!!!
lol, seriously if you hate on VLOOKUP you don't deserve to use Excel.
@@tecwzrd exactly !!! #LongLiveExcel
Of course XLambda came up with a wonderful solution. He’s a an Excel wizard like you
He and Bill Szysz are much smarter than I am with creating formula solutions. That is why we are so lucky to be a Team!!!!! Go Team!!!!!!
I wrote to MS regarding F9 evaluation key bug inside LET function, hopefully it will be resolved. Keep following it up. In addition to that I wrote user ID, pwd in Name Manager, so other user can't see inside of the formula by accessing name manager. Lastly pdf import functionality in Excel is very basic, like if we would like to pull certain things from invoice, say date, invoice #, amount...we can't target that data specifically. Looking fwd to have their resolutions from MS development team.
That is awesome, Syed Hassan!!!! I hope they will resolve these issues and make things better for us. Thanks for the efforts!!!
This is so cool. Thankyou sir
You are welcome! Check out the next video too: ruclips.net/video/xbuUcG8pZQg/видео.html
Wow.. very cool!! Thanks Mike and ExcelLambda for this great example. Go Team :)) Thumbs up!!
Go Team!!!!
Boom!What A Super Cool Formula!Really Hope The Microsoft Team Make These Available In The Not To Distant Future...Thank You Mike :)
I hope so too. But try Exceλambda INDEX formula below it is even better : )
Hello Mike. In my organisation we are using office 365 but still Lamda function is not available. Do you have any ideas why it is not available?? Thank you in advance
I am sorry, I do not. Microsoft does not even release the new functions to MVPs like me in a consistent way. It is really frustrating. : (
Only thing that depresses me is this is still in beta :( MAP and LAMBDA needs to be in production ASAP.
No worries, try Exceλambda INDEX formula below it is even better : )
We can also use LET Function correct ??
LET is for when you have repetitive formula elements and want to define a variable and evaluate variables one time only and save results for rest of formula. LAMBDA is for defining reusable functions or for creating function values that you can use in related functions like MAP and BYROW.
@@excelisfun Got it sir..
Thanks for the video🤗👍
Thanks Mike!
You are welcome, Kevin!!!!!
Lucky we have a great Team!!!!
How can we make use of LAMBDA & SCANs function in solving Tricks No. 33, 34, 35 of Taxes and Commission :)
Still going strong.
Yes, the Team is strong!!!!! : )
I use Microsoft 365 and still haven't got the LAMBDA function. Grrrr.
I hope you can get it soon!
link workbook download, sir
Thanks for letting me know. I just fixed it : )
Go Team!!!!