### Intermediate Excel

Course
Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

### Video Transcription

00:01
So let's continue
00:02
with age. Look up and we look up, commends or functions
00:07
and ah,
00:08
I'm going to explain everything for real. Look up because it's much more often used on dhe. Everything works for H. Look up, just it's ah
00:17
transposed. So we look up is looking Collins age hookup is looking at Droz,
00:22
so we look up functions, looks at the value in the left, most cone of table or selection or range,
00:30
and then returns of value from same row. But from defined column, meaning you defined and calm in the strange.
00:41
And if what you're looking up is matching with the entry in the
00:49
left most cone,
00:51
it will return to data from some other corn in the strange
00:56
Some threat too complicated and actually be able Cup is one of the commands that give hard time to pretty much anybody who starts first using it.
01:04
So imagine we have some sales data here,
01:07
and, uh,
01:11
we want to select the discount,
01:14
and based on this council, it's a
01:17
you can select whatever is year,
01:19
and we want to get how many units were ordered to in order to get this discount. Is there a correlation between the amount of discount and the number of units ordered. We just want to take a look it,
01:36
so we're not
01:37
going to use that Some for some further calculation. This this command is good for analyzing the data,
01:45
so we're going to say,
01:47
Man, look,
01:49
Coop.
01:49
And then
01:52
so the look of failure is what we're looking for. It's this
01:56
and then table array
01:57
is this.
02:00
And then we have the column index number.
02:06
This is called E, but it's not became a type B here.
02:09
We have to type 12345 So it's the fifth column in the range,
02:17
and we have to type here false.
02:22
And I explained that
02:23
in a minute.
02:24
So if you choose seven, we get the units or that
02:30
six.
02:31
So wyffels, because we look up, expect
02:36
the data in first comb of the range are sorted
02:43
in some way, either ascending or descending.
02:46
And if we don't have that, we have to put false so that it's going to be exact match. Otherwise, this is going to be a problem for Ville cup commend.
03:00
Also, what is very important
03:04
is that
03:05
we have two different to same entries in the different positions in different rose within the range
03:14
we look up is going to find first and return the value.
03:17
So we have more than one. We're going to disregard
03:23
any of these other Williams except the 1st 1 in the range as it is displayed
03:29
of the screen.
03:30
So this is the limit off the local function have multiple entries of the same value, which we're looking for. We're not going to get them because we look up, Ken,
03:42
return just one.
03:43
And there is no option to say Toe, look up. Okay. Giving the 1st 1 or the 2nd 1 or the 3rd 1 or
03:51
10th 1
03:53
It just works with the first. So this is the limitation off. Look up functions I was talking about,
04:00
uh, we look up function is mighty powerful, but it has limitations.
04:05
The 70 things is for H. Look up just then. Ah,
04:11
the table would be entered so that we have the
04:15
the designations off data in the first cone and they're entered in Droz. Most of the people don't do that.
04:26
That if they do angel copies for them,
04:31
So the next one is hyperlink,
04:34
and it's Ah, it's where a simple function.
04:39
04:43
toe, a certain
04:45
outside something
04:47
in in in the field, in and sell in
04:51
excel.
04:53
So what's this outside can be. It can be, Ah, some file
04:59
or, for example, some video that you want to play and it's on your hard drive and then you just create the full path and you get what you were looking for. So, for example, here,
05:11
05:14
I will get
05:15
the link to hyperlink function on Microsoft site. So this is the link that you have in blue in the in the box, describing the hyperlink function when you go toe
05:29
insert function,
05:31
05:33
and you get this link. So this is the
05:38
05:41
So this is it.
05:44
It can also be It could be good point to a file or something else.
05:50
Um,
05:53
so the next function is index,
05:57
and it returns the value of reference of the cell in the intersection of Runcorn given range.
06:02
How to explain it
06:04
other than one example.
06:09
So, uh,
06:12
and we hear type index,
06:15
we open it, and then we define the ray
06:19
and then we say Roll number, Let's say two
06:24
and let's see Cole number mystery
06:29
and
06:30
it's 56. So road to come three.
06:34
No,
06:35
as some other look up functions, when you look at it like this, it makes absolutely no sense to use it. You can just 0.2
06:45
no
06:46
C two,
06:47
which we get
06:49
with these numbers. But if these numbers are variable and come from some other calculation, or it's used in a loop in
07:00
in, ah, visual basic program or macro
07:03
and it makes absolute sense because then, for example, in the loop you can stroll to through every row and every column and do something with these numbers.
07:14
So this makes absolute sense
07:18
and the next function is indirect,
07:21
and it returns the value, our reference of the cell in the intersection off road and corn given range.
07:30
Um,
07:30
it's, ah
07:32
slightly different than,
07:35
uh, what you have seen
07:38
the previous.
07:40
So we have here references of the cells.
07:44
So we see
07:46
indirect
07:48
and then
07:53
07:56
We close it
07:58
and get 23 because a one is 23.
08:01
If you have clicked on the
08:05
three,
08:07
we would get 67 because indeed, three we have C one, which is 67 now. This also can be, uh,
08:16
uh,
08:18
designation of, ah, cell within the range that has bean calculated in some way getting some data using some fine functions in something like that
08:28
and that it would make sense. This also makes no sense, but it would make absolute sense if we would create this C one from some inputs that we get from some fine functions or look at functions, we will cut function, whatever.
08:46
So
08:46
this concludes this part off
08:50
lesson 3.4.

### 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

Milan Cetic
IT Security Consultant
Instructor