Transcripts
1. 01. Introduction: Hello, everyone. First of all, thank you for taking this class. My name is Pranay. Andi am, a business consultant for many off us, Excel is the savior, right? Our daily jobs, whether it be at the or in order it from O. R in the school or a university. So it helps us a lot in the number crunching work. So in this class we will be looking at one of the most used but less understood functions that is the wheel of a function. So how this class is structured is flushed. We will have an introduction to the Excel formula stab that is here. Then we will look at the structure of the formulas. Then we will move on through the structure off the look of function be aan den. We have a few examples toe. See how this really look up, bugs. Before we start with the village function, I would like to introduce you to the Excel functions as well so that we can have a look at word excel can provide. And what is the structure off the functions that are built within Excel Onda? What different kinds off functions are available as well Once we go through those functions and the structure, then we can have a look at that. We look a function onder How the structure of the we look A function is similar to all other different functions present in excel that we go through the structure. We will then look up the different examples that I have bolstered in their different videos after this introductory lesson. So firstly for looking at the functions are available, we can go through this the formula tab at the top off your excel way, Have a formula stab where if you see we have multiple functions such as the financial functions, then logical functions. We then have a text function. They look up and all. So the relook a function that we will be going through it later in this class, it allies within the look up and reference functions. And like many of us, it builds with different parameters which are important to the biblical function and based on those important, it gives us an outward. So the structure off all the functions generally that are bringing available in excel is that all the functions when we have to use them, they start with an equal to sign. Then after that, we put in the function name. For example. If we have toe do some calculations, so we will put into some function. Then we have a car leap. Bracker Not sorry, Carly Bracket, but normal bracket if you're not this the Excel also shows us hints for all those functions . Within the some function, we are able to see that it has number than common number two and so on. Structure off. All the functions have similar to this. Some may have these internal perimeters and some men are So, for example, if we take the sum But, uh, no way can either directly put in the number for which we want to some. Then we can go toe another number than another number, then close the record and then but press either and enter or the control shift ended. So we got to some different. Better for the same function can be some on Bracker. Then we will select this sell and the next cell are again. The number in this way work the hotels by putting in the cell is that if you change the value in the cell Some can still be calculated without you changing the formula or the selling region to calculate. So we will go through all these in the next videos, starting with the structure and then moving on. So we look up and then towards the end, with some examples letter to are we look a function So we'll come again to this class on we look up on day, I'll be happy to help you See you in the next lesson. Thank you.
2. 02. Excel functions: most of you must have used some kind of spiritual programs such as Microsoft, Excel or Rubles Richard, to make some charge, track your expenses or do some other number crunching. So in this class we will be working with Excel, mostly to help with all the number crunching and allow these times of works except provides inbuilt formulas which are raised under different heads, such as the look up in reference air. The Logical head today functions mad functions, text functions and many others. We come up across these functions normally vita number crunching as well. For example, we find some off multiple numbers using the some functions or the autism that is inbuilt. Then we find whether two criterias match. Using the IT functions, we find the current date using the date functions and many more we will be specifically looking at. We look a function later on in this class, but before that we will also look at the function structure in the next election. So see you there
3. 03. Excel Function structure: VLOOKUP Introduction: Welcome back in this section, we will look at the function structure. All the functions in excel follow general structure, that is, they have a name and an opening bracket and the closing record. These closing and opening brackets may or may not contain some input parameters. In this slide, you can see that the we look up also follow the same structure. The function starts with the name that is we look up then it has a bracket to indicate that the function and started dysfunction has what is called perimeter to identify the imports and the criteria that the function will work on Within the brackets, There are then four perimeters. Within this particular we look a function and then the closing bracket to indicate the end of the function. So generally speaking, all the functions President in Excel will follow this with some having an input bit in the package and some not in the next section. We will look at what the four parameters are that we look a function are rich, are indicated in through the perimeters within these closing and opening records here on what they do. So after that, we will then have a look at some practical uses of the function through a few examples. So see you there
4. 04. Understanding the VLOOKUP parameters: welcome back. The first perimeters here in the look of function is the look of value. This is the value that the real bookable start searching in the data set. The value air indicates that you want the function to search for this value within the range provided this range is then the second input for the function. This is called a table. Every once you have indicated what you want to be served, you know, indicate where do you want the search? Toby? This may be a single column or multiple columns. Date of it in the same sheet, different sheet, but a different file altogether. All sort of be noted that for the look of such toe, the look of value must be within the first column of the range. The card in port here is now the column in next number. To understand this letter. Suppose we are looking for revolution. The card collar off the rain, selected previously. So Bill under three. But if supposed there is only was one single column it will be the column in Next number will be one. Now you must be confuses. Why would you require them a column in Let's if the look of value needs to be in the first column own. This is where we look up is used as a reference function and not a look up function. Now again, supposed you ever Data set off some names and their addresses on another data set with the same names. But with Linge with their spouse is now. These two data sets are in two different files or two different shoots, and you want to link these two now. In this case, the link is the name, which is the first column of both that it asserts. So this is our first value, the look of value. We are now searching for the spouse name, which is the second column of data set to. So the range the second perimeter been second data set, and the column in next here will be to coming to the final perimeter, the rainy look up. This indicates whether you want to have an approximate match you enter through in that case , or if you want an exact match, U. N Befalls will look at these in the examples in the next section
5. 05. Practical use of VLOOKUP: Example 1: Hello. Welcome to the exercise section. Now, as you can see, I have my Excel sheet open with the different shoots named as the data set, one that doesn't to example 1213 So I'll start with the data set. One. What the day doesn't that I have created is a collection off student names with their schools, therefore different schools, their marks on the edges. The age range lies between 16 and 19 so you can see their total of 1100 different students with four different schools, their marks and the age. Then we have a second data set that contains the same set off 100 students. But in this case, we have the second column as the city where they belong. These are different cities out off these 100 students. We have around 45 different cities. They belong toe. In this first example, we have a list off pen students whom you had dot the data set, one that we had seen earlier. Is the list off students selected for a scholarship from four different schools, you know want to check whether any of the students are Some of those students have made the list or not. So for this we will be using that. We look a function with the use of the data set one on. But let's start now. So, as you remember, the structure contains the name of the function that will be We look up here, the value that we want to search, then the range in which we want to search the column that we want to search on whether we want an approximate match for an exact match collector start in excel. Every function or formula starts with the equal to sign. So after that we put in the name that we're seeing The big L o. Okay, you, as you see Excel also provide some hand. So it looks for the value in the left. Most column offer table. After that, we can either enter the brackets by ourselves are best stab. So when you press tab again, Excel provides a hint off the parameters that is looking for the look of value. People are a parliament legs number and the rain look So what we want to search is that his weather Alexa MERS Middle Easterner. So, for the first value off the perimeter, the look of value. We can either select the cell that we want to such or we can directly enter the name with credible course. But here we would select the sell directly. After that we put in a comma. The second is the table area. So from the previous videos, we have come to know that their table air is the range in which this name this column valuable visas. So our table area lies in the data set one which again lies in ah, different chic. So we go toe that sheet, we want to set the name so the name is in the second color. So we click on the first cell. We select the total 100 names by either dragging the cell or by selecting control shift under down key. Once this total 100 student is selected, we again come to the top field where the function is visible. We put in a comma, then it moves on to the card perimeter That is the parliament next number. So from again from the previous video, we have come to know that the column in next number is the column number in which we weren't the search to be done. So in this case, we have just selected on leader name column. So we will only put in one because we have only one column Tobe a sudden again we put in a comma on. Then we notice that Exelon's provided us him for an a prop off approximate match Re select True. Or for an exact match, we will select falls. We will, in this case, Elect falls because in the case of names, they might have the same starting names or the last name. So in the in that case, the approximate match will not give us the proper answer. If we have multiples name names, so we will select the Falls Press again Tab and the closing track. It's now, once this formalize complete, we can either just press enter. So in that case, the value will be calculated for our selected cell in the example she But instead off Presidenta, we will select control shift and then ender. So this also calculates the value off the formula. But what it does is that it copies the formula to be used within all the cells off the same column. So in that case, we will just have to go to the end off their cell. When the plus sign appears, we will drag down in the end. Said so in this way, what the formula has done is that it took the data. Now, in this case, we have just put in a tree, which was the Alexa must the from column A. But we see that it has taken the formula, has taken all the names and surgeon that does it. But how did that work? So, like I said, when we selected control shift and the formula got copied and when we move on to the next cell, we will see that this we look up itself actually, in the data this value of a three today four than in this case, if I and so on When we ran the function, this values the first value every changed way for the name five. As with each cell change, also, we noticed that the data set range are searching from B two B three me for be five and so on. So because of this when we had dragged it down, were the system brought the Excel function system? Did was that it considered this to be checked in that trains. Then when it moved down, it says for road Roger Hogan Intruder data set not starting from the first value, but from the second still 100. Next, Italians. So what this means is that we look up function is looking for the data data range in the data, sir Toby Sorted. But we do not want that because these might still be in that data set rain but mark in the sorted order. So if we look at the souse even list, we will see that this name appears in the car troll. But in our example, it shows that it is not found. So in order to correct that what excel provides is that when we select the data range, we need to also in port the dollar sign with the data rains. What this will do is that it will fix this be column and fix this range from the second row . Build 100 Castro instead, off moving down with each value each row. In our example, she So again, if we go to the end, can put control shift and on again tracked this down. So then we see that this Susie Villas has appeared. Now what has happened is like when we saw earlier this when this day three was changing to a four. If I it is still there. But if we go toe the data set range, But earlier it was B two and B three and before, but after putting in the dollar sign this be to Toby 101 for each row. It is still there, while this a 123 is changing. So it's better if we put in the dollar sign. It defines that our data set range, in which the value has to be said that not change but this the value being said there's changed. So this corrects our search for this student name in the data set one and this concludes our first example.
6. 06. Practical use of VLOOKUP: Example 2: Welcome back. In this second example, we will use that we look up function to find that a part of students whose name is entered here again. We will be using the same data shirt one where we have the name school, the marks on their age. So what we will do in this case is that we will use the parliament next to find our what is the age, the school and the marks for the name of the student that is entered here. Now, suppose we want to find our We just take an example Copal Lucas. Okay, we will go with this name. Yeah, So we want to find out what is his name? Which school he belongs to And what marks the ticket took it the scholarship. So, first of all, what reveal a kendo is that we will put in the Quito sign. Then we look up Press tab. We want to such this look of value. That is the name. And again the data set One important comma. We will go to the data set one. No, since we want to find out the age which is now in the fifth column off this data set. But according to our selection, it will be the 4th 1 we can check from this one, bro. Four column than number of rows will change. We move on to the right. Number of columns will change so we can identify the column number. So here, since we have selected the column starting from B to E. This is the fourth column the age. So we will again first select the first drop. Then again, either drag known or control shift enter to select the whole data set. Strange. Once we have selected the range, we will again correct, according to our last session, for $10 saying here dollars saying here again, dollar and dollar, this will limit. Arrange toe these Pacific be toe who even 01 and not move down with it sell if we want to do that. But in our example, we will be having only one column so we can leave it that that as well. So after that, we put in the Kama. Now, from this example, we will see what this column in next does. So in this case, we have the age in the fourth column. Off are selected range on we also in our example one the age to be found for that name which has been entered. So we will enter Thor, then comma and again we want the exact well. Do we found shift and control ship Enter. So what again? This we look up has done is that it took up this value. Whatever it was entered in the A six sell it went toe that data set range Beato even 01 b two, that is Tell us, George two e 101 That is, until the last year. No, look, when we move on to the column in next number We heard this age in the fourth call and we wanted age according toe the name that had been under. So our look up is with the name but we want to find out the age So the entered value in this column rain jewel for Andi. Then again, we want an exact Matt. So we did faults. So again, this tree look a function used, that value what was entered here to find out the corresponding age value in the fourth column. So once this is done, we can either start a fresh on In order to find out which school he belongs to, we will again put we look up, then select the name comma goto the range again. We can either limit our rain selection toe just these two columns, or we can also have up to whatever call it rains we want to select. So again I'm selecting the whole columns. Just toe tell you one thing that even if we select more columns than required, this column in Let's Range will limit the search to that column only. So since the school lies in the column to. But we had selected this range from 12 column for we can limit our search for this school. Toby found only till column, too. So again we put in the dollar sign, Mama falls close and controlled. So when we look at this, this be fine. They're for open Lucas school is full of one again. The result. It's cool one. So similarly instead, off creating the whole function again. Why I had selected the four columns in that range is because we can use the same tojust GOP dysfunction control. See then press escape. Goto that column very one The marks to be found ported Puerto, then control We based and again control shift before doing control ship tender. We know that the mark's lies in the third call. So the chain that toe three then control shift, enter And we see that we have got the months Now This is for open Lucas. What if we change it? Toe some other Namely, we can look for Jeffrey. Great. So we compete here. Goto that example Problem. Change it here. We do not have to again right the whole formula again For Jeffrey Grey. It found out it is from school three. He got 18 months and he's 19 years off a So a demons will three in 19 years off. So again, this concludes our second example on, we will move on to the next example. See you in the next list. Thank you.
7. 07. Practical use of VLOOKUP: Example 3 & Conclusion: In this third example, we will use that we look a function to combine to take assets. So we have this data set one that we heard used in the last two examples one with the names with school marks and age and the data said to containing the same names on the cities now , supposed these were in two different files are two different sheets and you would not have access to this different shoot later on. So what you want to do is you want to identify this? The city's off All the students, Toby, just put in against the name in the data set one so that you do not need the data set to again. So in order to do that again, what we will do is that we will go to the data set. One we will put in equal to in the F two. We look up. Then again, what we want to do is that for this name we want to find out the city. So before going to that, what is the common value here in these two data sets is the name name is in the second column. Name is in the third home. It may also be in the for column, but it does not matter. So what we will do is that we will start with a look of function. We look up, select the name comma, go back to a data set to in this case again, we want to select the same names. And the second column that that city control shift down Peter Select all again $14 science . Or that this range did not move. Then, since for this name we want this city, which is in the second column, Toby found report any comma, Comotto comma falls. We want exact cities to women, then can Coach of Tender And we will see that for the last. The city name is Freeman. That is three month California. So again, if we just click on here or drag down or w care, all the names of the cities for each of these students is taken from this data set to and put in here. So this we look up can also be used apart from searching too much to defend data sets. So with this, we conclude our we look up function and what this was useful to you. In case you need to asked some questions or queries, you can put it in the comments. I have also provided these example. Data shoots for you to use and test on your own. Thank you. Bye.