Here is a cool example of a LAMBDA function with a LET and XMATCH function. Point to two cells that contain movie names and the function will find the cast who is in common between the two movies and provides the photo of those people in common.
If you want to play the Six Degrees of Separation of Kevin Bacon, download the Excel file from here: Tom_Hanks_From_Excel_Lambda_And_Let_2397.xlsx
If you want to play the Six Degrees of Separation of Kevin Bacon, download the Excel file from here: Tom_Hanks_From_Excel_Lambda_And_Let_2397.xlsx
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2397.
LAMBDA and LET return pictures from data types.
Hey, welcome back to the MrExcel Netcast.
I am Bill Jelen.
I actually got this idea from one of the Excel hash entries.
You know LAMBDA and LET are these new functions from Excel.
They're pretty wild.
And so what I have here is I have a couple of dropdowns where I can choose a movie.
This is a data type.
The Wolfram Alpha data types here.
Mmovies, right? So the data validation is returning a movie and I have a LAMBDA function defined called InBothCasts.
It's pretty wild to me how much is happening inside this Lambda function.
So the first thing I do is I pass it movie one and movie two from these two cells.
And then LET cast one.
Well, here, let's take a look.
So Cast-One is Movie1.[ Cast and Roles ].Cast.
It gives me a list of all of the people.
And then over here Cast Two is Movie2, (so now we're pointing to B4).[ Cast and roles ].Cast And then between the two of those arrays In-Movie-2 is a variable that says do an XMATCH to see if this item Tom Hanks is over in the second list.
The little hash symbol there means that we're using the whole array.
And originally.
I didn't have the IFNA, so it returned a bunch of.
NA errors because XMATCH, unlike XLOOKUP does not have the IFNA option.
So undo that.
And so you see that now we're getting either a positive number if it's there, or zero if it's not.
Alright, then matching cast is the FILTER of cell B6 hashtag, which is cast one with in movie 2 hashtag, and that gets me a list of just the people in both.
So now I have a list of the people who are in both casts, right?
Then I can find the photos as matching cast dot image and the names is matching cast dot Name.
And that was all because of this guy named Geoffrey Blake.
He was in a bunch of movies but for some reason he's not in Wikipedia.
At least his picture is not in Wikipedia.
He was returning a #FIELD! error because Wolfram Alpha doesn't have a photo of him.
So IFERROR photos if the photo returns a #FIELD! error, then give me the names right?
So who was in both Apollo 13 and Footloose?
Kevin Bacon, I'll put this Excel file out on the web with a link down in the YouTube description if you want to give it a try.
Cool use for LAMBDA there to return an image or actually a series of images.
This example is in my new book, MrExcel 2021, Unmasking Excel click that “I” in the top right hand corner for more information about that.
If you like these videos, please Like, Subscribe and Ring that bell.
I just got to the 100,000 subscribers and we thank everyone who subscribed.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
LAMBDA and LET return pictures from data types.
Hey, welcome back to the MrExcel Netcast.
I am Bill Jelen.
I actually got this idea from one of the Excel hash entries.
You know LAMBDA and LET are these new functions from Excel.
They're pretty wild.
And so what I have here is I have a couple of dropdowns where I can choose a movie.
This is a data type.
The Wolfram Alpha data types here.
Mmovies, right? So the data validation is returning a movie and I have a LAMBDA function defined called InBothCasts.
It's pretty wild to me how much is happening inside this Lambda function.
So the first thing I do is I pass it movie one and movie two from these two cells.
And then LET cast one.
Well, here, let's take a look.
So Cast-One is Movie1.[ Cast and Roles ].Cast.
It gives me a list of all of the people.
And then over here Cast Two is Movie2, (so now we're pointing to B4).[ Cast and roles ].Cast And then between the two of those arrays In-Movie-2 is a variable that says do an XMATCH to see if this item Tom Hanks is over in the second list.
The little hash symbol there means that we're using the whole array.
And originally.
I didn't have the IFNA, so it returned a bunch of.
NA errors because XMATCH, unlike XLOOKUP does not have the IFNA option.
So undo that.
And so you see that now we're getting either a positive number if it's there, or zero if it's not.
Alright, then matching cast is the FILTER of cell B6 hashtag, which is cast one with in movie 2 hashtag, and that gets me a list of just the people in both.
So now I have a list of the people who are in both casts, right?
Then I can find the photos as matching cast dot image and the names is matching cast dot Name.
And that was all because of this guy named Geoffrey Blake.
He was in a bunch of movies but for some reason he's not in Wikipedia.
At least his picture is not in Wikipedia.
He was returning a #FIELD! error because Wolfram Alpha doesn't have a photo of him.
So IFERROR photos if the photo returns a #FIELD! error, then give me the names right?
So who was in both Apollo 13 and Footloose?
Kevin Bacon, I'll put this Excel file out on the web with a link down in the YouTube description if you want to give it a try.
Cool use for LAMBDA there to return an image or actually a series of images.
This example is in my new book, MrExcel 2021, Unmasking Excel click that “I” in the top right hand corner for more information about that.
If you like these videos, please Like, Subscribe and Ring that bell.
I just got to the 100,000 subscribers and we thank everyone who subscribed.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
