8 hours 33 minutes
so the final lesson this model is about, look up functions
and look up functions are those functions that makes Excel extreme for two.
So you have seen how we can work. They raise how we can do some math calculations, how we can manipulate text.
But Indy and Excel is about getting information from Road eight entered table off course. There are some other usages of excel bit in most applications. We get a lot off lots of numbers or textual datum, and we want to extract something. We want to analyze that data. So
in most of the cases with old look of functions, you can find what you need
and combined with math and text functions and Aires. They can help you get almost any calculation from daytime saying almost any
because there is a limit to capabilities of look functions.
And, uh, if you want to go further and they were really complex analysis of data, our do something really complex, useful with it.
Ah, you will need some micro's and some programming because
they're simply some things that haven't bean
programmed in tow, Excel look of functions.
So we're starting with the function called dress and it creates seller references. A text. It's a relatively simple function.
you put, uh,
the number of throw and then the number of colon.
And then you put this absolute number, which is actually way how you define what they're going to in what form you're You're going to get the address back. So if you put four, you get all
ah, you get the address, See, for meaning for throw
there had calm. So it's C four
in a relative for mitt.
And if you put the one, you will get it in absolute for mint.
And if you put too,
just the row is going to be absolute and put three.
Then the column is going to be an absolute form, earth and roads going to be relative. So this is
what dysfunction does. So you get the address off the cell now in this form, and it actually doesn't make any sense, because if you know the constant off the roll and calling, you can just
typing by your own. But if these air variable numbers that come out off some kind of calculations, some kind of fine functions, some kind of look up functions,
then you can do a little bit more. Also, if you include that in some kind of macro or visual basic for application program, then you can do really powerful things.
Next function I'm going to talk about is choose and basically chooses the value erection to perform. And to understand that with me, put it this way. So
it chooses third number from this list, and this list is a one. A two, a three. And I'm choosing the number three
now the again, the way I typed it. It makes no sense because I if I'm seeing a one a two, a three and I'm choosing the third number from West. I know it's a three. Uh, the power of this function comes when these air variable,
uh, perimeter's, which I can then later pick up from, for example, here, at least one of the variables this one is one of the parents is variable. So basically, I want to
create the product off this cell and
the 1st 2nd and third cell from this range.
So, um, I have this e one and then we have cell C one sea to see tree, so If I put here one,
I will have
a two, which is 11 multiplied by 33 which is 363. And if I changed this to three, I will get 726 which is 11 multiplied by 66.
Very simple. Now, if all of these variables can be chosen from some calculation, then you will actually get what you really want
again. This can be really, really great if you're using this even macro,
So formula text is the next function
and it's simply retails formulas text it also
pretty much makes no sense by itself. So we have four cells. We have, ah, in fifth sell some of these four cells and here we say formula text off a five. What we get is
text that represents this formula.
Now then, we can use this to manipulate this text and return. It is the formulas somewhere else. And this is the power and
importance of dysfunction.
And the final one we're going to talk about in this part off.
This lesson is get people data
and get people data simply extracts data from the table. Now, usage of this
function is rather complicated
typing it so that you do it properly can be real
problem. And if you look here, we have some road data. And then we created the pivot table from the true data.
we created here
some cells that point they record here
to this one. This one and this one.
Uh, we didn't do it by typing. So if we say
get people data me, open it, then we need the data field, which we're going to point to the people table, et cetera, et cetera. It can be really complicated thing, too.
So what? We can do it. Instead of that, we can just click on the cell and we get
all the things we need about
get people date. The function here is Well, just press enter in that cell
and excel does it for you.
And this is how I suggest to use Get people. They function if you need it.
So this is it.
And if we change the filter
and includes another
Yeah. Okay. So these cells the
they're in a way off this. You updated people tables were going to delete them. But we have these here, and
this is very
direct information of these three cells. If we now includes third
customer, get it even
for all of them. And we get to the full number here.
And if you look at this some here, it's equal to the grand total we have here. So if we need some data from the tape people table, you just select these cells
and you can put them in some other calculation, probably in some other worksheet.