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

It creates a hyperlink

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

if I click here,

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

so hyperlink

05:33

and you get this link. So this is the

05:38

identical link like the one we had before.

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

click here.

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.

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

Similar Content