Shoutin' Sam has an Excel question.
This video shows many ways to answer different questions.
=FILTER() in Excel
=XLOOKUP in Excel
=TEXTJOIN in Excel
=INDEX(FILTER()) in Excel
This video shows many ways to answer different questions.
=FILTER() in Excel
=XLOOKUP in Excel
=TEXTJOIN in Excel
=INDEX(FILTER()) in Excel
Transcript of the video:
Shouting Sam: look through B4 to B20 for less than 1200.
Give me the name from C4 to C20.
My solution: FILTER.
We're going to filter all of the things and see where B is less than 1200.
It will give you a long list like that.
Nope, he says 1 cell.
Alright then TEXTJOIN.
We're going to join both the comma space in between, ignore the empties.
If B4 to B20 is less than 1200 give me the name from C4 to C20.
Otherwise quote quote.
“Not just one cell, we want just the first one!” So I want to go back to my FILTER function but I'm going to wrap it in the INDEX and ask for the first row.
Although wouldn't it be better to find the person closest to 1200 but under 1200?
So that's 1199 out here.
In the XLOOKUP, the match mode find the value just less than, and we get Kelly, who is closest to 1200.
Shouting Sam: :there's some empties.
The XLOOKUP works perfectly with those empties, but the INDEX and FILTER don't.
Add a times before to be 20 greater than zero.
There's got to be dozens more, but I can't do it in less than 60 seconds.
Give me the name from C4 to C20.
My solution: FILTER.
We're going to filter all of the things and see where B is less than 1200.
It will give you a long list like that.
Nope, he says 1 cell.
Alright then TEXTJOIN.
We're going to join both the comma space in between, ignore the empties.
If B4 to B20 is less than 1200 give me the name from C4 to C20.
Otherwise quote quote.
“Not just one cell, we want just the first one!” So I want to go back to my FILTER function but I'm going to wrap it in the INDEX and ask for the first row.
Although wouldn't it be better to find the person closest to 1200 but under 1200?
So that's 1199 out here.
In the XLOOKUP, the match mode find the value just less than, and we get Kelly, who is closest to 1200.
Shouting Sam: :there's some empties.
The XLOOKUP works perfectly with those empties, but the INDEX and FILTER don't.
Add a times before to be 20 greater than zero.
There's got to be dozens more, but I can't do it in less than 60 seconds.
