What do you do if you want to do a LOOKUP? So VLOOKUP, XLOOKUP whichever you have. But instead of returning one match result you want to return all match results. You might do a search online and discover some crazy
functions that do the job. But that was the past. Your future will be different. It's going to have a filter in it. This video is brought to you by Excel Dynamic Arrays
Beginner to Expert course. Be one of the first at
your company to understand how formulas work in the new Excel. Think outside the box and come up with simple solutions to complex problems. For more information go
to xelplus.com/courses. The filter function is one of
the new dynamic array formulas that's a part of Excel for Office 365. Here are four reasons why
Excels filter function is great. Number one, filter returns
multiple match results for one or more LOOKUP values. Number two, you can
filter data without having to refresh your filter, so that's something you need to do if you use the advanced
filter feature in Excel. Number three, you can nest
it in other functions. For example, if you want
to sum or count the results you can put filter inside the
sum or the count functions. And the fourth one is it's super easy to write a filter formula. I'm curious to see what you think though after you watch this
let me know if you think it's an easy function to
work with or not so much. (upbeat music) So here I have some information on Division, Region and Revenue. I want to specify the region here and I want to filter the data set based on my specification. So basically I want to find all matches. So let's say I put Asia in here. I want to get all
matches from the SATA set that have Asia included. Right, so it's kind of
like I'm doing a VLOOKUP but returning everything. So let's start off with filter, my array is the data
set that I want filters in this case I want to return all results, the next argument is what
do I want to have included, and here comes the logical test. So I want to have these rows included as long as they equal Asia,
which is in this cell. If it's empty I can define what to return. If I leave it empty it will still work only if it doesn't find
any results in here then it will give me an error. So I'm going to leave it
empty for now and press ENTER. Okay, so my formulas spills
and I get all the results back that include Asia. The formatting doesn't come with so I have to highlight the cells where I expect to have
numbers and format them the way I want. So let's right mouse
click, go to format cells or use the shortcut key CTRL+1 and let's format these as a number. Now if I switch this to
Europe, I get Europe results. If I have nothing in here
or if I just put something that doesn't exist, that's
the new #CALC! Error that I'm going to get. So to overcome this error I'm going to add the last argument for this and say if it's empty, return not found. One thing you can do here
is to also add a drop-down. Now you can also use the
new spill array referencing for drop-downs but that's something that we're going to cover
in the feature section. Right now I'm just going
to add a simple drop-down to this, go to the data tab,
go to data validation tools, click on list for
source, I'm just going to say equals, these and say OK. So now when I click on Australia I get all the values for Australia back. Now we can dig a little
bit deeper in this formula if I highlight this and I press the F9 key so F9 is great for debugging. And if you want to
become advanced in Excel and if you're doing complex analysis or creating complex formulas,
you're going to need the F9 key. Check what happens when I press this. I can see what's behind this. So how Excel basically translated this in a way that it understands. These false values belong to these. So the first four, these are false right? There's no match, then we have a true. That's for Australia, right? So that's how filter translates
that include argument and then it filters
everything that's true. And that's what you see here. Now whenever you use F9 in your formulas, make sure you don't press enter, otherwise this is going
to stay in your formula. You're going to want to
press CTRL+Z to go back or press escape to leave the formula. Next let's do this one. We want to filter for
divisions and regions that have revenue greater than a number that we type in here. Filter, our array I'm
going to include everything just so that we can
see the correct numbers are filtered, include,
so this time we need to take our revenue column into account and include only values that are greater than this number. If nothing is found,
let's just put not found and that's it. All of these are greater than this. And if I change this to
3,000, I get everything back. If something is blocking it, so let's go back to 45,000
and let me just type in something like note here and I switch this to zero, press enter, I get the #SPILL! error. Notice that when I click here it tells me it wants to spill here,
something is blocking it, the moment I remove
this the formula spills. One thing I want to show
you before we wrap up this section is to use table referencing. So we're going to do the same thing we did in the first example. I'm just going to copy this drop-down and paste it here but this time we're going to use table
referencing for this data set. So when I click here
notice that table tools are activated which means
I've already converted this to a table I've called it TableDiv2. Now let's use the FILTER function on this so my array now is the entire table so all I have to do is
hover to the side of this until you get this tilted fat arrow, this way you can highlight
the entire data set. Next is what do we want included? Well, we want the region so just hover over the column until you
get the fat down arrow, if this equals Australia. Okay, otherwise let's do our
not found and enter, okay? So this way I'm using
the table referencing and the advantage of this is the moment the data set expands, my filter
is going to expand as well. So let's say I have a new
Health Division in Asia. It's added here automatically. Now I'm going to do one last thing here and that's what if you want to filter for two different things? So what if I want Division for example to be Game and Region to be Asia? How would I write my filter formula? My array is the whole table. Now here I can take advantage of Excel's Boolean Logic
in the include argument. So I would have to write
the formula like this. I'm going to open bracket
because I'm testing for more than one condition. And whenever you do that
start with an open bracket. I'm going to test if Division equals Game and I want Region to equal Asia. And here I need to use
the multiplication sign. And right now you might be confused especially if you are not familiar with how Excel handles Boolean Logic. But we're going to cover this in detail in the advanced sections. I'm just covering it in this section so you're aware that this is possible. So we're going to get region and check if that equals Asia. Here, close bracket. This is my include
argument and the last one let's put our not found in there as well. This way I get Game and Asia. If I look for Game and Europe, I get that. Now if I look for health
and Europe, I get not found. So that's Excel New Power LOOKUP Formula. Let me know what you think
in the comments below. If you liked this video,
give it a thumbs up, and for more information on a
Excel Dynamic Arrays Course, check out the link in the description or go to xelplus.com/courses. (upbeat music)
