Today we're going to take a look at a really cool formula solution. Is actually a trick with
a new filter function, we're going to use it to
return multiple match results from non adjacent columns based on any logic you need in your analysis. You can also use this
to return unique values from non-adjacent columns. It's one single formula that spills and gets the job done
for you in record time. (upbeat music) In our first example, we want to get a list
of names and positions of employees that have a salary
that's greater than 100,000. Now we want our results right here. And we want it be in
a dynamic way so that, whenever we update this number, our results will automatically update. Our raw data is right here. And, we have some additional
information than what we need. So we also have information on department, start date and birth date. Now just for simplicity, I put the data set beside where we're going to do the analysis. But of course, the raw data can be on a separate tab as well. Now my data here goes
all the way to line 68. What I'm going to do is to turn this into an official Excel table, so that whenever I add new lines to this, my results will also automatically update. So let's do that first, just go somewhere inside the
data set, and press Ctrl T, my table has headers. Now, I'm just going to
take away the formatting and update the name to
Tsal, for table salary. Now what I want to do here is to use a formula because
I want this to be automatic. I don't want to have to
press refresh or anything, I want my results to update
the moment something changes. This means that I'm going to use the new Microsoft 365 filter function for Excel. Now if you're not familiar
with the filter function, I have a separate video on that, I'm going to add the link to
the description of this video. So check that out. In this lecture, we're going to use a trick with that function so that we can return
non-adjacent columns. Let's start off by using the traditional filter function first. The first argument is the array. This is the columns
that you want returned. What I want returned here
is name and position. But check this out. Name and position aren't
beside one another. And for array, I can just have one range. So I cannot select them individually. What I have to do is
select the entire range and also include start date and birth date in there even though I don't
want them in my end result. But we're going to take
care of those in a second, so stay with me. The next argument is the include argument. I want to include only two rows where I have a salary number that's greater than this number. So I'm going to highlight salary and greater than this one right here. If you also wanted it to be
greater than or equal to, you can add an equal sign here. In this case, I just want to go with a greater than. Last argument in filter
is an optional argument. What do you want returned if
there are no matches found. So if no one is earning more than 100,000. Well, in this case, I don't need to worry about that because I know I'm going to have a match. So I'm just going to close bracket, press enter, and I get a
filtered view of my results that's dynamics. So I get Wolfgang
because he earns 116,000. Then I get Paul. These ones are not in my results. And then I get Stevie Bridge right here. I notice also, that I
get these two columns. I see them as numbers because they're not formatted as dates. But these are basically my
start dates and birth date, which I actually don't want
because I just want position. So here's what you need
to do to filter out what you don't want. Check this out, we're going to
use another filter function. The array is basically
this, what we see here. Now what do we want included? Well, for this include argument, I'm going to use array constance. So I'm going to open the curly bracket. And I'm going to flag the columns that I want to keep by
using ones and zeros. So check this out. In the original array, I
selected four columns, right? Now we're going to say
what we want to keep, I want to keep the name and I'm going to put a one comma, I don't want to keep start
date and birth dates. I'm going to put a zero comma zero comma. I want to keep the last
one, I'm going to put a one and I'm going to close the curly bracket. So in a way, what I'm doing here is I'm broadcasting the
one to devalue sitting in this column. What the include argument or filter does, is it includes values that are true, and four Excel, ones are
true and zeros are false. So this is a way that you can
flag what you want to keep. Now check this out, I'm
going to close the bracket and press enter. And I only get the columns
that I want to keep. Now I first saw this
from one of my students, Adam, who's in my dynamic array course. And I loved his solution. So I had to share it with
you because it's so simple, and it's really easy to remember. Just flag the columns
that you want to keep. Now in case you want to get this sorted. You can continue and wrap this
inside the sorted function. By default, it's going to sort
based on the first column. So I'm going to close bracket press enter. I have everything sorted by name. If you wanted to sort by position, you can add the source
index argument here, which is the second argument. So I'm going to put a comma here, and sort by the second column. So that's going to sort by position. Now you can use this trick
for other things as well. So you could use it to get a unique list for department and position. So basically a unique list
of non-adjacent columns. Now let me quickly show you
I'm just going to hide this. So we're closer to our data set. The unique function is also
one of the new functions that was introduced in 365. It basically returns a
distinct lists of values. So if I wanted just a
distinct list for department, I can select department here, close bracket and press enter. Now in the same way, you
can get a unique list of more than one column, but they have to be beside one another. So I could return a unique
list of department and name. In this case if they're
beside one another, it's no problem. But that's not what I want. I want to get a unique list for department and position right here. The problem is they are
in separate columns. Here, I'm going to use
the filter trick as well. First, what I'm going to
do before using unique is to filter out the columns that I want from my original list. So I'm going to highlight
the entire array. And then in the include argument, I'm going to flag the
columns that I want to keep. So I want to keep the first
one that's a one comma, then 000 for name, start
date and birth date. And last one is for position I want. So I'm going to put a
one, close curly bracket, close bracket and enter. So now I have department and position. Then I can put this
inside my unique function, and I can get a unique list
for department and position. Now if you want to get it sorted, you can put this inside
the sort function and sort. In this case, we're going to
sort by department, right? So a very simple flagging solution to only include the columns
that you want returned. Now, remember, I said we want
everything to be dynamic. So let's just test for that. Let's unhide this. I'm going to change this to 120,000. And my results update automatically. Now let's also make sure that
if we add new lines to this, everything updates as well. So I'm going to add a
new legal department, and let's just add a consultant for 50,000. Now, we shouldn't see this
in our first list here, but we should see it here. We see the legal department. And if this person gets a raise, and they earn 130,000, we should see Lilli right here. As a bonus tip, let's
see how we could sort these based on their salary. Here, what I would do, is first just filtered
information that I need. So let's start out with filter. My array is basically
what name and position but I'm going to include salary
in the first round as well, just so that it's easier
for me to sort it, and then I'm going to exclude
it from my final result. So that's my array. Next, what I want to
include is just like before, I want everything included, that is greater than this number here. Right, so that's my filtered result. Now everything is going to spill and it's not going to have enough space because I have these in front. So let me just insert some lines. Just so that we can see our numbers here. And anyhow, we're going
to be restricting it to two columns in a second. It's just easier to write the formula. If you start from the
inside and then go out. Next is to get this sorted by salary. So I'm going to sort, my array is this and I want to sorted
it by the last columns, that's 1, 2, 3, 4, 5. And I want it in descending order. So I'm going to put a minus one. Close bracket, press enter. Everything is sorted by salary. So Crystal Doyle is first
and Stevie Bridge is last. Now, I only want to include it, the name and the position. So let's put this now
inside our filter trick. That's my array. My include argument are my
flags of what I want included. That's one, then zero for start date, zero for birth date, one for
position and zero for salary, because I don't want to
show that in my end result, close bracket, press enter. I have everything sorted in
descending order by salary. That's the filter trick you can use to return values based
on non adjacent columns. I love the simplicity of this. If you're also a fan of this solution, do give this video a thumbs up. Now I have a complete course
and the new Excel functions, where I show you how you can maximize the potential of the new Excel. Check it out if you have
Excel for Microsoft 365, link to it is below. Now before you leave, don't forget to subscribe so
you can join our community here and stay up to date with
new functionalities. I'm going to see you in the next video. (upbeat music)
