Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:00
we're continuing this
00:02
lesson with the lookup function
00:04
and look up function
00:07
is ah,
00:08
simpler version of bee. Look up in H look up
00:12
functions
00:13
and it's basically there for backup. Oh, are backward compatibility with previous versions of Excel.
00:21
Um,
00:22
my shoulder, once so look up looks a value. The first argument from one row or common an array
00:31
and returns the value from the other column or row in the same array.
00:39
You don't have to specify the whole ***. It's much simpler to use, and sometimes it makes more sense to use it
00:45
and just to see how it's done. So we have. Look up,
00:50
We're looking for this value here.
00:54
And then we look at this range here and we expect the answer from this range. Here
01:00
it has the same limitation, like
01:04
ah,
01:06
the V Look up.
01:07
It expects that all of these air different or if there are two of the same,
01:14
it will return the 1st 1 So, for example, if he
01:18
are looking for Johnson and we,
01:23
they have Johnson here.
01:25
We won't get Jenise. We'll get Mike because Mike is the frizz Johnson in a list.
01:30
And it's simple. Is that So, uh,
01:36
this is a limitation off dysfunction, but it works very nicely if we know that all off data is different.
01:44
So, uh,
01:46
nothing special about it. Just continue with their functions.
01:53
So next on his match
01:55
and Millie match returns a relative position often item in the ray.
02:00
So let's, uh, look att how it
02:04
works.
02:07
And, uh,
02:08
we say here mad
02:13
and then we look att This will you here.
02:16
And we have look up array. And this can be this
02:23
reaches clothes and presenter and we get four.
02:27
But if we air, for example, in this function going that from position or one that from physician three get to. So if we're looking for this number here which can be a variable because it can also be a seven,
02:45
it will give us position within the rare. Also, this disarray doesn't have to be,
02:50
you know, just 12345 It can be whatever it can be.
02:55
A list of numbers which are random
02:59
again. The limitation of my trunk shin is that it will simply stop when it finds the 1st 1 Because if you type seven here is well,
03:09
nothing will change
03:13
it won't find this one. It will find just the 1st 1
03:15
What can be done if you're looking for more of them that in the next search you can start from position which is this one plus one?
03:28
We had that with text strings when we were looking for the second space in the street. Take string. So you understand that
03:36
the logic is the same
03:37
in slightly different here. So for example, here we would go
03:42
to say
03:44
match, then open
03:46
and then we look
03:49
this
03:52
and then we would need to look from the cell, which is it would make things complicated. We will need to use some text functions and then other functions.
04:02
I won't go into this,
04:04
but
04:06
it can be done.
04:11
So the next function is transposed
04:15
and transpose is actually the last function that we're going to mention here.
04:23
And transpose simply transpose is a row into cone and coal into row.
04:28
And I show you this for on an example. So we have these six numbers we want to transpose them in. Thes excels. So we have to act like this is in a ray function because it is
04:41
miss a transpose open bracket. Selecting the ray.
04:47
It has to be the same size.
04:50
And, uh,
04:58
it doesn't work like that. Yeah,
05:00
and this is very simple because I haven't pressed control Shift enter.
05:04
It's a very nice too
05:10
six.
05:11
Come here and press control shift. Enter.
05:14
And now we have gotten
05:15
the
05:16
array in horizontal form.
05:19
So this mistake was actually
05:23
here to teach you that when you were working with the race? You don't forget to press control. Shift, enter.
05:29
This is why we got this wrong result of the first place.
05:32
And this concludes the lesson about functions. Of course, there are other look of functions, but at this point, I don't think they belong at this level off course.

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor