VLOOKUP, MAP & LAMBDA functions to spill a Two-Way Lookup Formula Excel Magic Trick 1760

Learn how to create an advanced array formula to spill two-way lookup results using VLOOKUP, MAP, LAMBDA and XMATCH Microsoft 365 functions. As of Monday, October 11, 2021, the MAP function is only in Microsoft 365 Insider Edition of Excel.
Topics:
1. (00:00) Introduction.
2. (00:41) Why VLOOKUP with function argument array operations does not work.
3. (01:29) How we will solve the VLOOKUP problem.
4. (01:42) MAP function
5. (02:14 )LAMBDA function
6. (02:26) VLOOKUP and XMATCH function
7. (03:40) Spill the array
8. (03:57) Conclusion
9. (04:14) Thanks to Excel Lambda for this formula!

[excelisfun]

Comments are not being posted by YouTube. There is a terrible bug that YouTube 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 :-))))

[Excelambda]

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 ✌

[richardhay]

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!!!

[mattschoular]

I say bring on MAP to the masses. Thanks Mike

[chrism]

Wow that was awesome Mike! Great video

[Al-Ahdal]

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.

[Al-Ahdal]

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.

[HusseinKorish]

That's Amazing Mike ... thanks alot.

[kirtithorat]

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

[spilledgraphics]

This video: dedicated to ALL those "v-lookups haters" 😝😝😝 amazing Mike !!! 👌👌👌👌 #GoTEAM 🏆🏆

[razamoulvi]

We can also use LET Function correct ??

[darrylmorgan]

Boom!What A Super Cool Formula!Really Hope The Microsoft Team Make These Available In The Not To Distant Future...Thank You Mike :)

[upigroup]

How can we make use of LAMBDA & SCANs function in solving Tricks No. 33, 34, 35 of Taxes and Commission :)

[tecwzrd]

Only thing that depresses me is this is still in beta :( MAP and LAMBDA needs to be in production ASAP.

[wayneedmondson]

Wow.. very cool!! Thanks Mike and ExcelLambda for this great example. Go Team :)) Thumbs up!!

[patrickschardt]

Of course XLambda came up with a wonderful solution. He’s a an Excel wizard like you

[ennykraft]

I use Microsoft 365 and still haven't got the LAMBDA function. Grrrr.