The next Excel dynamic array function introduced at Ignite 2018 is the FILTER function. Use =FILTER(array, Include, [value_if_empty]). This is one of the new functions that is entered in a single cell, but the results spill into adjacent ranges.
To download the workbook from today: https://wwww.mrexcel.com/download-center/2018/09/filter-function-in-excel.xlsx
To download the workbook from today: https://wwww.mrexcel.com/download-center/2018/09/filter-function-in-excel.xlsx
Transcript of the video:
Learn Excel from MrExcel podcast episode 2235: The Filter Function in Excel.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Brand new function an ARRAY formula, a modern ARRAY formula; the FILTER function.
so I want to get all of the red cells from that range over there. =FILTER FILTER, here's the array.
Which ones do I want to include? I want to say where this range is equal to red.
And what do I do if there isn't anything? Let's just leave that empty for right now.
All right. So there's all my answers, copy the titles over, name, team, score.
All right, but do we have to hard code that? No, of course not.
Let's add a little cell up there So if B4 to B16 is equal to F1.
Dollar signs?
No, no need for Dollar signs. All right, see we got less answers.
Ask for red, I'll get more answers. It automatically expands on-the-fly. Ask for yellow...
CALC error.
CALC error. That's a brand new error. This means that there's currently no answer that will appear.
But it's possible that it will give us an answer later, when we get yellow.
Now to prevent the CALC error, let me get change that yellow back to blue.
Prevent that CALC error, that's where that third function comes in and we'll just say, none found.
None found. So that's the 'if empty'.
Press enter and we get none found. Change to red and it will return the answers.
I'm loving these new ARRAY functions Mike Garvin's Control Shift Enter book spent an entire chapter on how to do this and now it is one single formula, Mike. Want to redo that book?
That book's going to go from 355 pages out to a much much less, if they have these new Excel functions.
Now my new book Microsoft Excel 2019: Inside-out doesn't go into as much detail as Mike Garvin's book, but it definitely will talk about these new ARRAY formulas.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Brand new function an ARRAY formula, a modern ARRAY formula; the FILTER function.
so I want to get all of the red cells from that range over there. =FILTER FILTER, here's the array.
Which ones do I want to include? I want to say where this range is equal to red.
And what do I do if there isn't anything? Let's just leave that empty for right now.
All right. So there's all my answers, copy the titles over, name, team, score.
All right, but do we have to hard code that? No, of course not.
Let's add a little cell up there So if B4 to B16 is equal to F1.
Dollar signs?
No, no need for Dollar signs. All right, see we got less answers.
Ask for red, I'll get more answers. It automatically expands on-the-fly. Ask for yellow...
CALC error.
CALC error. That's a brand new error. This means that there's currently no answer that will appear.
But it's possible that it will give us an answer later, when we get yellow.
Now to prevent the CALC error, let me get change that yellow back to blue.
Prevent that CALC error, that's where that third function comes in and we'll just say, none found.
None found. So that's the 'if empty'.
Press enter and we get none found. Change to red and it will return the answers.
I'm loving these new ARRAY functions Mike Garvin's Control Shift Enter book spent an entire chapter on how to do this and now it is one single formula, Mike. Want to redo that book?
That book's going to go from 355 pages out to a much much less, if they have these new Excel functions.
Now my new book Microsoft Excel 2019: Inside-out doesn't go into as much detail as Mike Garvin's book, but it definitely will talk about these new ARRAY formulas.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel
