Welcome to Highline Excel 2016 class video number 12.
Hey, if you want to download this Excel workbook,
Business 218 video 12 to 14 start
file and follow along, click on the link below the video.
Hey, last video, video number 11,
was an epic video about LOOKUP formulas and functions.
For this video 12, and 13 and 14,
these are actually individual topics for LOOKUP situations
that were not in the previous video.
Now here is a common LOOKUP situation.
What if we need to look up the server name and the event
in order to return status?
So here's our data set, server, name event.
We need to actually have one, two LOOKUP values.
Now remember, in order for VLOOKUP or LOOKUP functions
to work for exact match, we need a first column
with no duplicate.
And boom there's two duplicates there,
are three duplicates there.
Ah but look, if we have this server name and this event,
notice combined together-- server name event
one, server name event two-- if we have these combined
in a first column, we would have a unique identifier
that VLOOKUP could use to look up the status.
Now in essence, there's two solutions to this.
There's the helper column and the array formula.
Most of the time, it is easier and more convenient to create
a helper column.
And that simply means we come to the front of the table--
if you want to use VLOOKUP-- equal sign,
and we're going to take the server name.
And notice this is table formula nomenclature there,
is relative cell reference-- that at symbol
with the name of the column, that will always
get one cell to my left-- and I'm
going to join it to the event.
There you go.
Now we will have a unique identifier, Control Enter,
double click, and send it down.
Go to the last cell, F2.
That is looking good.
Now we can simply use equals VLOOKUP,
and here in the LOOKUP value we'll simply join.
There's the server name.
We'll join it to the event, two LOOKUP values
joined together, comma.
And the table, here's our new unique identifier
in the first column.
Four columns, notice that the table formula
nomenclature, that's the actual inside entire table, comma.
The fourth column is status, so we type a 4
so VLOOKUP will retrieve the status, comma, and 0.
Close parentheses, Control Enter,
double click and send it down.
Go to the last cell, F2.
That is looking good.
Now I should have shown this first.
If you use VLOOKUP-- and let's just
say we tried to look up the server name-- comma,
in the original table right here,
comma, 3, because this table has status in the third column,
comma, 0, close parentheses.
VLOOKUP match, when we're doing exact match,
it's programmed if it sees duplicates,
it only gets the first one.
So when I Control Enter, double click and send this down,
even though this is event two, VLOOKUP
had no way of knowing because it sees this,
it only finds the first one every single time.
All right, now the next question is this.
If you couldn't have a first column unique identifier,
then we would have to, in essence,
simulate this column inside our formula.
Now that means we're going to join server name
column to event column, so this will be a join array operation.
Now we're going to use index, because what are we
trying to get in the array?
We want status, comma, and now we simply need a row number.
Well match is perfect for finding the relative position,
and we're going to join a server name,
join symbol to our event, comma.
And this is where we join the two columns.
So I'm simply going to highlight the server name
and use the join symbol and get the event column.
And now we have, in the LOOKUP array argument of match,
performed a join array operation.
Now let's just click on this argument and hit F9 key
to evaluate this.
And notice that it looks exactly like our helper column up here.
So we have simulated the helper column in our formula, Control
Z. Now that LOOKUP array argument inside of match
requires Control Shift Enter to get this to calculate
correctly, so when we enter it, we'll have to remember that.
Comma, we are doing exact match 0, close parentheses.
And there it is.
Inside of row number, there's the match function
to deliver the relative position of server name and event.
We come to the end, close parentheses.
We have to use our special keystroke, Control Shift
and Enter.
I immediately go up to the formula bar
and look for the curly brackets to verify that it correctly
calculated as an array formula.
I can double click and send it down, go to the last cell, F2,
and it's looking good.
Now there's actually one other amazing trick we can use here.
I just want to remind ourselves about index.
We used this a lot last video, but we never
took advantage of the fact of this array argument right
here inside of index can handle array operations
without Control Shift Enter.
Not only that, but we also learn, comma, last video,
row number.
If we want to retrieve all the row numbers,
we tell index with a 0 or leave it omitted.
So watch this.
I'm going to click Escape.
We're actually going to copy this entire formula
and do some more really crazy here.
Notice we, in LOOKUP array, have this array operation that
requires Control Shift Enter.
But that's a whole column, right?
I can simply wrap index around it.
And there it is.
That array operation is now sitting
in an argument that can handle array operations without using
Control Shift Enter.
So ready?
I'm going to come to the end.
And since I, comma, I want all of the rows from that column,
I put a 0, close parentheses.
Now this is kind of strange.
We are only using index here as an extra function
to avoid Control Shift Enter.
Now if you can remember Control Shift Enter every time,
then by all means do it this way.
But if you don't want to have to use that keystroke,
that will work.
Watch this, Control Enter.
I look up in the formula bar.
I don't see any curly brackets.
Double click and send it down.
Come to the last cell, F2.
That is looking good.
All right, so if we have two LOOKUP values,
we can either use index match index or simply index
and match with this join array operation.
And remember Control Shift Enter.
For both of these formulas, we do not
need this first helper column.
But by all means if we want to just use LOOKUP and join those
up to LOOKUP values and add this extra helper column,
that is the way to go.
All right, next video we'll actually
talk about another LOOKUP situation.
Not when we have two LOOKUP values,
but when we have a single LOOKUP value
and we need to return multiple items.
All right, we'll see you next video.