Welcome to Highline Excel 2016 class video number 13.
Hey, if you want to download this file,
business 218 video 12 to 14 start
file and follow along, click on the link below the video.
Hey, we're still doing-- in video 12, 13,
and 14-- LOOKUP formulas.
And here's the situation, one lookup value
to return multiple items.
Now here's the one lookup value.
And if we tell our LOOKUP formula
to look through this column, the first column,
well there's duplicates.
In our last video, we saw what happens
when we have duplicates.
Our LOOKUP formulas don't know how to automatically
then pick out, for example, event one, event two,
event three.
So here, we want to look this up and return
down here the different events.
If I change the one lookup value to this server,
then I need to return event one and event two down here.
Now, there's no built in LOOKUP function that can do this.
And fundamentally, the problem is
when I select this particular server,
I actually-- need in my LOOKUP formula-- row three,
or relative position three, five, and six.
Once I have all those, then index function
could go ahead and pull the third, fifth, and sixth value
out.
Now before we do our formula here,
we are going to have to count, because the count is going
to be part of this formula.
So up here I'm going to say equals count
ifs the criteria range.
I'm always going to be counting how many server computers there
are.
So right now there are three.
Now I want to come down here and I'm ultimately,
as we have done in the last couple of videos,
we're going to use index.
And in this array-- I'll highlight this whole array
here-- the problem comes from row number,
because we have multiple row numbers.
So inside the row number is going
to be a big array formula that creates
the relative positions-- meaning in an array it will create
three, five, and six-- and then we will have
this small function that will pull out, as we copy
the formula down, relative position three, five, and then
six.
I'm actually going to escape and start on the inside.
I first want a formula element here
that gives me all the relative positions, meaning one
all the way to seven.
And the most robust way to create this
is to use the row function.
And I'm going to look at any one of these columns in the data
set, F4 to lock it, close parentheses.
Now row would simply give me five, six, seven, eight.
So if I hit F9, you could see that's not
what I want, Control Z. So from that I subtract row,
and I'm going to take row of the first one.
Notice that's five, F4, close parentheses.
Right now we'll say 5 minus 5, 6 minus 5.
When I evaluate this whole thing,
it gives me an F9, 0 all the way to 6.
I really want 1 all the way to 7, so Control Z, plus 1.
Now I close parentheses.
Now there are other ways-- we could use the row function
and highlight rows A1 to A7, but this right here
is the most robust.
If I insert rows above or below or move it or do whatever,
this will always give me an array of relative positions.
If I highlight this and F9, and notice
there is my complete list of relative positions, all
of the possible records, but I only
want three, five, and six in this resultant array, Control
Z. So I'm going to choose to use division, and then ask,
hey, are any of you in this column-- F4
to lock ir-- are any of you equal to the server name-- F4
to lock it-- close parentheses.
That gives me trues and falses, F9.
Notice a false will be in the denominator
so the divide by 0 error will be our filter.
When it sees a true, that will take relative position three,
divide by true, which will be three, Control Z.
Now I can highlight this whole thing,
and when I hit F9 there is our filtered list of just
the relative positions I need.
And I would like to use the small function wrapped
around this, so as I copy down, I can pull out
the first smallest, the second smallest, the third smallest,
Control Z.
Now I'd like to use the small function,
but the small function will require Control Shift Enter.
But since 2010, we've had the aggregate function.
Now the aggregate function has been around since 2010,
it has a lists of functions.
Only the functions 14 to 19 can handle array operations
without Control Shift Enter.
And there it is, there's small.
So I'm going to select 15 as our function.
That tells aggregate that it is now the small function, comma.
I'm going to ignore error values.
There's the six that will ignore those divide
by zero errors, comma.
And there's the whole array.
F9, there it is.
Small is going to be looking at that, Control Z.
Finally I come to the end, comma.
And the K, I need one, two, three, four as I copy down.
So we use our formula element for creating sequential numbers
and formulas, rows.
Now I'm sitting in E11, so I type
E dollar sign 11 colon E11.
Notice this 11 is locked.
This one is not.
Right now rows reports how many rows are there from 11 to 11.
Well there's 1.
But that 11 will turn to 12, and then 13.
Thus rows will report how many rows, 2 and then 3, and so on
Close parentheses, now let's just see if this works.
This is aggregate as small.
And as I copy it down, it is going to work fine.
There is the three, five, and the six.
Those are the relative positions inside of index.
So index, array is always the values
we want to go and get and bring back to the cell,
so those are events.
F4 the lock it, comma, and there it is.
Row number, that whole big aggregate
with its rate calculations is simply there
to deliver the right relative position to index.
Close parentheses, Control Enter, and copy it down.
Now we'll fix the num errors in just a second,
but let's go ahead and try this, boom, boom.
Each time it is looking at a single lookup value
and returning multiple results.
Now to deal with the num, I'm going
to notice I counted up here.
So as I go down, when it gets past, in this data set, row 3,
then I want to show nothing down here.
I want to only run the formula up here.
When this automatically changes to two,
then I want the index to run here
but I want to show nothing all the way down.
So that's easy enough, we can use the If function.
And watch this, I'm going to come to the end,
click on K, Control C, because in my logical test right
there-- oops-- I'm going to Control V. I'm
going to saw when rows-- that's the number incrementer--
one, two, three as I copy down, when you get past two--
and I'm going to lock it with the F4 key--
if the formula is past row two, comma, then what do I want?
I don't want to show anything.
Double quote, double quote, that's
a zero length texturing that will show nothing.
Otherwise wise, if I'm not yet past row two,
then please run your big array formula.
Now this is an example of a formula where
you don't want to use If error, because If error
would rely on the fact that this array formula would have
to run in every single cell.
Right now, when I have only two records being returned,
index will have to only run twice.
The rest of the times If will just dump this zero length text
string into the cell.
Close parentheses, Control Enter,
double click, and send it down.
And there we come up here.
Instantly we get our result.
Now that is pretty complicated, because there's
no built in way for a LOOKUP formula
to return multiple matches.
You know, if you remember back to access,
our study and business 216 the pre-req class,
really the solution would be to use access,
right, and simply have this is the criteria and server name
column and the event being returned.
However, this is a common enough request and task in Excel
that this type of formula will do it.
All right, next video we'll have our last video, video 14,
about LOOKUP formulas and functions.
All right, see you next video.