Transcripts
1. Introduction: Welcome to this course, master using VLookup in Excel, including the advanced options. I'm not I am and I am a chartered financial analyst. I have been using Excel for several years. Bringing my expertise and experience will help you to learn the various aspects of VLookup in a very successful way. Vlookup stands for vertical lookup under so not the most commonly used to look up functions in Microsoft Excel. Use VLookup when you want to find the details from a table or a data range. For a given lookup value. You can use one or more data tables in VLookup. Vlookup function looks up a value in the leftmost column of a table and returns the corresponding value from o nor more columns to the right. In this course, you will learn about the syntax of VLookup function. The rules that govern building up of the argument. The errors that you may encounter while working with VLookup. Under the best practices for better results. You will learn VLookup usage from several real-life examples. So start a strength of function better. There is a class project that you will leverage to reinforce what you will be learning in this course. Let us move on to the first lesson.
2. The Syntax: The VLookup function comprises of four arguments. Number one, What do you want to look up under that is called the lookup value, where you want to look for it. This is the second argument and it's also known as table array. The column number are the column index number in the data range that contains the value to be returned by the function will be the third argument of the function. The fourth and final argument is approximate or exact match. This is also known as the range_lookup argument. Let us see these arguments in data. In the first argument is, What do you want to look up, also known as the lookup value. This should always be in the first column of the table. The second argument is the data range or the table array, wherein the lookup value is located. This is also known as the lookup table. If your data ranges, for example, from V3 to 17, then the lookup value should be in the first column, that is column B. The third argument is the column number or the column index number in the range that contains the value or the search result that is to be returned. If your data ranges meet three to 17, then you are first column will be b, second column will be c, and so on. The last argument is optional. Use this to specify if you want an approximate match, exact match. If you only need an approximate match, you can leave that argument as blank, or you can enter one or true. On the other hand, if you want an exact match, you should always specify a value a that as 0 or as false. With this, let us move on to the next lesson to discuss about the absorbed on exact match.
3. Approximate or Exact Match: We saw about the syntax for VLookup function in the last video. In this video, we shall discuss the fourth argument of the function, where you specify the type of match you want is an approximate exact. First, let us understand the approximate match. The approximate match is the default to one in the VLookup function and is optional. That is, when you leave this argument as blank, VLookup considers that you are expecting on approximate match. Alternatively, you may also enter a that one are true for this argument. When you use approximate match, it is recommended that you saw to the leftmost column of the lookup table in our sanding, or a2 is target the correct today sells. The approximate match will come handy when you are looking to return values like tags, commission, discount, grade, et cetera, that is based on a graded scale for a given range of values. With this, let us see more about the exact match. Unlike the approximate match here. For an exact match, you need to provide the necessary argument and the VLookup function. You may either enter 0 or false for the last argument in the function. This is the widely used option on this, we'll return on exactly matching written value. If not, it will throw the not available yet. We shall see the examples for both approximate and exact match in the subsequent lessons. With this, let us move on to the next lesson to learn about the rules that govern the VLookup function.
4. The Rules: In the last video, we saw about the usage of approximate, our exact match and the VLookup function. In this lesson, let us discuss about the rules that govern the way lookup functionality ensures that the lookup value 0s in the first term leftmost column of the table are the data range. For example, if your data ranges B52, 17, then you are first column should always meet me in VLookup, the lookup value goes from left to right, are not on the reverse. That is why the value you are looking up should always be in the leftmost column of the table. The data you want to retry are the result of values can appear in any column to the right of the lookup column. The lookup value should be unique. Yells, the VLookup function will stop at the first occurrence of the lookup value, starting from the first row of the data table. When going down the rows, the VLookup function is not case sensitive eternally retrieval the matching data irrespective of the case in which that has been entered in the table. With this, let us move on to the next lesson. We'll look at an example that returns a single value using VLookup function.
5. Example: Return a Single Value: In the last video, we discussed about the roles governing VLookup function. In this lesson, let us look at an example to understand how to return a single value by using VLookup. The data in this worksheet relates to purchase of different products from several sublayers. Here, let us use the VLookup to search and return the value for a given purchase order, we shall use the exact match here. So let's build a VLookup function. I select this cell yen seven, where I am going to enter this array lookup function. I enter equal, and then I enter VLookup to bring up the VLookup function. Then I select PL seven, where I will be entering the purchase order id. And then I enter a comma, then select to that data range here. Then I enter a comma. And then since I am going to return the P0 value, I need to enter the column IT here. So that is the fifth column from left, 12345. So I entered file here, and then I enter a comma. Since we need a exact match, either you can enter 0, R select falls from the argument. Then close the argument, hit Enter on your keyboard. Now you get the purchase order value for the given IID in cell L seven. If you change this, for example, then the value changes accordingly. This is how you use VLookup argument to return a single value using exact match. With this, let us move on to the next lesson to see another example that returns more than one value in a single way lookup function.
6. Example: Return Multiple Values: In the last video, we learned from an example about returning a single value by using VLookup function. In this lesson, let us look at another example to understand how you can return more than one value by using a single VLookup function. We shall be using the same purchase order data that we use to for our previous lesson. Here. Let us use a single VLookup to search and return multiple values, that is product, supplier and p-value. Here also, we shall use the exact match. Select these three cells. Enter sine to bring up the VLookup function. Enter. We look, open the bracket and then select the cell where we will be inputting the lookup value, enter a comma, and then select the data range. Now, open a special bracket. I'm gonna enter the column values from where the values needs to be returned. Here you do it with two comma six, comma five, and then close the special bracket and then put a comma and then enter 0 for exact match. Then close the bracket to close the VLookup function argument. Now on your keyboard. Control shift enter. Then the VLookup function, we bring up the product, supplier and the p-value for the reward or ID entered in column E. And this is all about building away lookup function to return multiple values for a lookup value. In this, under the previous example, we constructed or VLookup argument on the same worksheet where the data is situated. Now, let us move on to the next lesson to see another example that explains how you can construct two-way lookup function. When you have a lookup data on different worksheets.
7. Example: Data Table in Different Worksheet: In the last lesson, we learned how to return more than one value by using the single VLookup function. In this lesson, let us discuss how we can look up the values when you are data.table a sinner, different worksheet. We shall be using the same purchase order data that we used in previous lessons. Here in this workbook, There are two worksheets, data one and data to. The data one worksheet contains the details like preorder number, product, supplier under logistics partner. The data to update contains p1 dot number quantity under. We need to bring in the details like product and supplier into this data to OK, she'd let me insert two columns to the right of the purchase order number under let me name that US products and suppliers. Now, let us bring it to the VLookup function for returning the values of product under sub player by looking up the data in data one worksheet. Here also, we will be using an exact match. So let us select these two cells. I incur equals a 100 and tied to a lookup to bring up the function. And then I select this cell C3, that is where my lookup value is. Enter a comma. And then I need to go to data one and then select the data range. Here. Select to the data rings. And then press here for annual chemo so that you make Curtis Up dilute reference. Otherwise, you will get the error when you copy the function from one row to another. Now insert a comma. Open the special brackets. We need to return the columns product and the product is in column two of sheet the data one, and then supply a reason three, closest spatial brackets. Put a comma and then put 0 for an exact match. Then close the VLookup argument. Now, press control shift enter on your keyboard. And VLookup returns the values of product and supplier for the PO order id and S3. Now you can track this term to other rows. If you want. You can check that as well. For 1-2-3-4-5 one, the product is match d and supply it to see IQR. So 12351 match d and AQR. This is all about using lookup data table from a different worksheet for the VLookup function. Let us move on to the next lesson, wherein we shall discuss how to combine multiple worksheets to return the desired values.
8. Example: Work with Data from Multiple Worksheets: In the last lesson, we discussed about how we can look up the values whenever data table is in different worksheet. Now, in this lesson, let us see how to combine data from multiple worksheets in VLookup function to return the details, we shall be using the same purchase order data that we used in our previous lessons. Here in this workbook, there are three worksheets. That's data one, data two data. The data one worksheet contains the date is like preorder number, product supplier under logistics partner. Their data to worksheet contains the preorder number, supplied quantity, and the price per unit. That data three worksheet contains the details of pizza order number under the discount percentage. Now, using VLookup, We are going to return values from each of the worksheets to build our new data table, which will show the product quantity supplied under the discount. It is possible that you can accomplish this via three separate VLookup organs. But the challenge is how to do that without resorting to three different functions. Let us understand how to do that. Let us start with the first column of values. That is the product that need to be returned. We know that this is available in the table in data one worksheet. So let's build the VLookup as we do now, Molly. So I select the cell here, default unknown. I bring up the VLookup function and I select s4, that is the value I will be looking at. Then I go to data one, undone select the data range. We are going to return the product. So that is in column two. So I enter column two, and then for an exact match, I put 0. On that night you enter. So now for the quarter 12341, the product value is correctly return. You can check it here. If you look at the function, the data range is coming from two data. One, look at that closely. If you have your worksheet name that contains spaces, for example, instead of data on ice, put it us data space ONE than if you look at the function now, the worksheet is enclosed in single quotes. If there is no space here, let there is no single quote within the worksheet reference. So let me copy this function argument from default to E4. So I am getting a value which is AAMC. So what is the AAMC? Amc is the supplier name for the pivot or 1-2-3-4-5 for n1. So what's happening here? So let's look at the function closely. You will notice that VLookup function is trying to look for the value in d four. That is the product name in the data range and data one worksheet under returns the value from the second column, that is supplier. So actually the data range, it has changed here. Instead of starting with the D2. Now this is starting with e two. So that is incorrect. So how do we correct that? So select to the cell D4. And we know that the value which we are going to look obese and column C. So we need to make that AS absolute reference. Select C4, and then press your four, cycle it through till you reach a point where the column is magnetize thumb solute reference are. You can also make that AS by simply entering the dollar sign in front of C. Now, let me copy this function to y4. Now, we are getting the error not available because we look up, is still looking at the worksheet data one. But we need to have the quantity supplied values returned here. But that's not available in data one OK shade, but that's available in data too. So we need to dynamically change the worksheet so that we looked up, looks at the correct one. So how did we do that? Let's use the indirect function. So you can use this indirect function to access the cell address. For example, when I give indirect S3, I get the value people order number because S3 contains the PVA order number as the texts here. So y3 contains quantity supplied. So let me put e3 here. Now for example, if I use a direct reference to these indirect function referring to the cell edge five, this returns the value quantity supplied. So how this is coming up, the indirect function refers to the cell edge phi, which in turn refers to E3 that contains the value quantity supplied. So this is what we are going to use to dynamics change the worksheet name within the VLookup function. So let us select the cell D4. We don't need to change this first argument, but we need to change the second argument to dynamically bring up the worksheet name. So let me put in directly here and then open the bracket. And let me put the remaining value in double quotes. And then let me close the argument. I give enter here, I get the same result as that our failure as the indirect function now translates the details within the double quotes to the correct cell address. Now let me copy this formula to E4. And therefore, as you can see, we get the product value returned for these columns as well. Because we look up is still looking at the data one worksheet. So we need to replace this data are one part within the indirect function to a cell reference. So how did we do that? Let me select default after the double-quotes here. Let me enter another double-quotes and then insert and symbol. And it takes two year, which refers to poke shade. So data one refers to the worksheet data one. Then I enter another and symbol here under Night puts up double-quotes. Then I enter. If you look at the functional, the indirect function within the VLookup function refers to the cell LFO, which is in turn points to the data one worksheet. So I get the same result because it points to the correct worksheet here. Let me copy this to default to E4 and year four. So I get the quantity supplied us 150805. Let us check that out. So 412341, the product is match tj. That's correct. Under the quantity supplied is one phi 08 R and the percentage is 5%. So all the values are correct here. Now let me select all these and then drag it below to copy the function to the remaining rows. Now I get the reference Sarah y. So let's look up the argument here closely. In default, the indirect function is pointing to yell for, armed in E For it is pointing to young for. And in year four, this is pointing to yen for, which is correct. But when we copy that to the remaining rows down below, the cell address for the UK shade has changed to l phi and then Yang five under AN file, which is incorrect. This is because the argument in d4y for under four within the indirect function are relative reference. Now we need to make them a sub solute. So select D4 and then select this cell. Yell for presto you have four. Similarly, you make M4 in column E as absolute reference, and then make N for us. Well, lesson absolute referenced by pressing F4. Now you can select to D4, F4 here four, and then drag it down to copy to the remaining cells. So VLookup now correctly returns the appropriate values for column d, e, and f for each of the numbers in column C. So if you want to check, you can check it. So let's check 12, W3, iPhone5, 1to W3 iPhone5 is match. Hey, let's match then 10961096, and then 5%, 0.5. so it works correctly. This is all about using data table from multiple worksheets in a single VLookup function. Let's move on to the next lesson, wherein we shall discuss how to combine multiple workbooks to return the desired value.
9. Example: Work with Data from Multiple Workbooks: We saw how to combine data from multiple worksheets in VLookup function to return the details in the last lesson, let us now see how to work with multiple workbooks in we look up here. For this example, we shall be using two separate OK. books, the workbook Data Table one contents 300 records under the other workbook data table to contents about a 150 records, both related to the sale of multiple products in different segments across different countries. In years 2009, 2023rd task in hand is to build a VLookup function that looks into both these books. Under returned the appropriate Wells used to build a new table. So I have another new book open and I'll have all shaped by name evaluate. Here I have a list up consolidated order IDs in column B that I am going to look up in the two books on return the matching values for columns u2. To accomplish this, we are going to use your urine function combining with VLookup. Now, let me first start building the VLookup function as we normally do. Let me do that for looking up one of the books, say Data Table, one, we have already seen in earlier lessons how to build a function when you need to return multiple values accordingly, select the range S3, 23, enter the equal sign to bring up the VLookup function and then select V3, which is we are going to look up in the data range. Now select the first toke book, Data Table one, and then select to them range there. Put the comma, we need to return the values for column 14, lowercase date. So open the bracket 14, then we need product that is in column four. Then we need unit sold that is in column six. And finally, we need the profit as well that is in column yum, that is 13th column. Then close the special rocket under we need an exact match. So let me put 0 there and then close the bracket. Now, if I press control shift enter, I should get the values for all these. But do you see that to VLookup has returned error not available for all the four columns for the operator in V3. Now copy this formula to the rest of the cells. Down below. Under you will see VLookup returns values for all such order IDs that it finds in data.table one that is the book won. But do you see that there are several rows for which the cursor returned DNA may be because the data is not available in data table, one of the workbook. So now let us extend to the function to include the other OK. Select the cell range C3 to year three, and let us 32 the function argument. Enter IV here before the VLookup, and then enter a comma after the VLookup argument. Since we'll be returning the same values from the second book called So the easiest way is to copy this IFERROR argument. And then face to that after the command provider, you held the same file path are the, you know, the second workbook name already, since we know both, let's do that. Now insert a comma or the end of the argument, and then insert two double quotes to indicate to return blank if no such values are formed, and then close the argument by entering to closing brackets, one for each of the IFERROR function. Now press control shift enter. You will see that this has returned a bland because we have not made the necessary corrections. So let us make the necessary corrections here in the second part of the argument. In start-up Data Table one, you need to change that data table. And then you also need to change the data range here. If you look at the second workbook, you know that the last 2301. So you need to enter this 301 here instead of 141. Now, press control shift enter. Now VLookup returns the values relating to date product units sold on the profit for the order ID in cell B3. But before we proceed further, let us spend a moment to understand the function. Starting with VLookup looks up the first data table, one you've found, it returns the values for column C2. Further row number three. If not, that is, if it goes to the second argument, looks into the second book, that is table two and returns the values you, nothing is found that it returns some black because we hope Porter double-quotes here. Here, you can improvise this formula. Instead of returning gland, you can insert a meaningful message like order not found. You do that by inserting the required texts within double-quotes. Now you press control shift enter and then copy this to the remaining rows. You can see, you know, you'll get values for all the rows except for row number nine, where the function has returned us not found. Let us check that out. Let me go to Data Table one. Bring up controlling Earth. So nothing is found. So let me go to the other table, data table t2, then I search here as well. Here also it's not form. Now we know that there is no such order ID light what is available in basics. That's why we look up here, straightened order not found. This is all about using multiple code books in a single VLookup function. Let's move on to the next lesson where we shall discuss how to combine multiple tables in a VLookup using IF function.
10. Example: Work with Multiple Tables using IF: In the last lesson, you learned about using VLookup, then you have your data in multiple workbooks. In this lesson, let us discuss how we can use VLookup to look at multiple tables using IF function. We shall also see how to work with approximate match. So here we have our data table, name the product sales, containing the details of the sale of multiple products in different countries. We shall be using two lookup tables containing the discount rate that will be applied based on sales volume range. For the country USA under the author for Austria, we need to look up the sales in column D from these two lookup tables, Table 102 to the right, and returns the appropriate discount percentage in column depending upon the country. As you can see, the discount percentage in Table one and Table two varies depending upon the sale serving. Since the sales from column D of the table, product sales can fall in any one of the sales ranges in the look-up tables. We need to use the approximate match. But before we proceed to build the VLookup function, let us name the data range and table one and table two. That way, it is much easier to manage the VLookup function. So how did we do that? You need to select the data range. Right click. And then click current, define name on the paper. You can give a name here. Say for example, if figures sales range, and then click OK. Then this named range will be created. We have already creator to such named ranges. One is t USA, under the other is T Austria. The details of which you can see in the name box. Now, let us build the VLookup function. Since that discount will be different for a then of the countries, we need to use the IF function along with the VLookup to return the correct discount rate, select cell E3, and then bring up the VLookup function. Select D3. That is what we are going to look up putting the comma and then begin the IF function, open the bracket and sell it to them. B3, which is the column country they've unlearned enter something like V3 equals ESA. Led to VLookup. Use the range TU ESEA. Yells, let that use T Austria. Now close the bracket that will close the if argument. Put a comma here, enter two, because that is the column where we know the discount rate that needs to be returned for the sales veins and then can turn one for approximate match. Here you can enter one or you can even click on through and then closing. You can also leave that AS blank. Now press Enter. We look up returns third discount percentage in cell E3. As you can see, we look up as Luke read.table one for sales in D3 undone. Let us return to the discount percentages 0, because for the sales syringe 010 thousand, the percentages 0 here. Now copy this to the remaining cells. And you can check for correctness. For example, in IIT for the function has returned 2% because this is for Austria onto the sales 14,515, which falls between ten thousand and twenty five thousand, where 2% is 30 is come for this range, for the same range, this will be one person for ESA are under. You can see that in row number 1011, this is all about using IF function in VLookup when you need to look up multiple tables to return the correct value. This is also about how you can use approximate matching. We look up, we shall discuss the error that you may encounter while working with VLookup in the next lesson.
11. Overcome ‘#N/A’ Error in VLOOKUP: In the last lesson, we discussed about using VLookup to look up multiple tables. Why are you function? We also saw how to work with the approximate match. In this lesson, let us learn about the most common error that you may end up with when working with VLookup function. It is possible that you do something incorrect way working with Luca. And they end up in some mirrors. The most common error that we will come across is NEA or not available. Let us understand the possible causes of this era under how to overcome the VLookup function will throw the NBA error. When the lookup value is not available in the lookup table, you may buried some error handling into the VLookup function. So as to take care of situations like this, you can use the IF function together with the VLookup so that you'll get a meaningful message than just third in error in this particular case, instead of just to the VLookup function, we have insert order new Farah under we have also given a customers stay alert message has no such order so that you get them meaningfully message rather than just getting a. You may also get the same nadir when the lookup value is not in the leftmost column of the data table has seen this example. Here. The order ID is in the second column. Hence we are getting the error. Always ensure that the lookup column is the first column of the data table. It may also be possible that the formats of the lookup value onto the lookup column are not the same. Here. In this case, as you can see, the lookup value is syntax, but to the data and the lookup column has been far metatarsal number are first-class. You may not notice this, but when you look closely, you will notice that the number has been stored a subtext, and it is left-aligned R2, you may see a small green triangle at the top left corner of the cell. So you ensure that the formatting is taken care so that you will not get in a era. You may also end up with this, any error when you are leftmost column or the lookup value contains some special characters like carriage, return, line, feed, et cetera. Here in this example, the lookup value has such a corrector asks You can see from the formula bar, once you remove that, then we look up, threatens the exact match here. Normally, you will come across such a situation when you import our copy data from a file or a website, et cetera, you may try using cleaner substitute function are both in combination to clean up the data. In such a case, trim function may not work always. It is also possible that there are unnecessary spaces, leading, trailing, or inbetween, either in the lookup value or in the values from the left-most column are invoked. For our example here, the order ID in row number four has space in between. That is why you are getting a error in near here, here. Then this may happen when you import our copy data from external sources. So you can clean up the data manually. Our use, Find and Replace, use trim function to get rid of all the unnecessary spaces, then you will not get the era. This is all about the DNA era. What costs us 30 era under how to overcome that? In the next lesson, let us discuss about some more errors that you may encounter when working with VLookup.
12. Other Errors in VLOOKUP: In the last lesson, we discussed about the NA error, the reasons for the same, and how to overcome that. In this lesson, let us learn about some of the other areas that you may encounter when working with VLookup. Besides the NA era, there are few more errors that you may come across when you use VLookup function. This includes areas like value name under f. Let us understand in this lesson the possible causes for these errors and to overcome the same. Let us start with a value. However, you are already aware that the column index is the number of the column to the right of the lookup column in the data table from where you want the lookup to return a value is there is some mad. However, when you enter the function, there might be a typo on the Yoder specified the column index number is 0. Since there is no such thing as a 0 column index, the VLookup returns a value here. As you can see, the VLookup function argument as 0 as the column index number. And a few, correct to that, to three. That is the sales figures. Then hit Enter, then the VLookup returns the correct value. With this, let us move on to the name error. The name error usually means that you have one meter to include the courts renewed formula. For example, look at the VLookup function argument here. We are trying to locate the order number for the product funnel, which I have entered in the VLookup function argument. However, I have orbiter to include that text within quotes in the function argument. Hence, the VLookup function is returning a name error here. In such a case where you referred to your name or a text in the function directly. Note though, include that within double-quotes. So let me correct that here. As soon as I enter the quotes for the text, and then you enter, we'll look up returns the correct value here. Let us discuss about the rest, sarah. Next, you may end up with the rest of error due to a couple of resistance. Let us see them one by 11. Reason for the reference could be that you have entered a column index number that is greater than the number of columns in the data table. Here in this example, the column index number has been entered eight, but the data table contains only four columns. Once you correct for the column index number, then the function returns the correct value here. It is also possible that you may end up with this error when you accidentally delete a column from the data table. Look at the example here. We lookup has returned to the correct sales numbers for the lookup value entered in cell H5. However, when a Delhi to enough the columns say column C. Then we'll look up, throw septa of error. You may immediately undo underpinning bug per column. Ah, you can adjust to the function to take care of the change in a data range. Yet another reason could be that you are referencing to another worksheet, R0 book into array lookup argument. But that worksheet or workbook is no longer available. Consider the example here. This VLookup function is referencing to another worksheet, that is the product OK shade and then returns the values here, has set, the function has returned to the correct values. However, if you delete to the worksheet, CFR Amazon climb deleting this worksheet product. Then the argument throws up, up Korea fear or do not delete a worksheet or that has been reference to inner VLookup function. This is all about the errors other than n0 in VLookup. Lets more to understand where you are. Vlookup function sometimes returns on incorrect match.
13. VLOOKUP Returning Incorrect Value: In the last lesson, we discussed about some of the other. They are not satellite to value name and address that you may encounter or you're working with VLookup. Now, in this lesson, let us understand why VLookup sometimes returns wrong value. You may end up with you or VLookup function returning incorrect values. Mostly because you are using an approximate match in your function or given. But you have not properly sorted out of the first column of the lookup table. So how do you overcome this? You can war come by this by two approaches. Let's see the first one. The first approach is to sort to the first column of the lookup table for you keeping the approximate match of the function or domain. Here you see that the VLookup has returned and incorrect match oil. The correct product is finite. It has returned to the product name for Sweden because the lookup column has not been sorted. When you sort the first column in ascending lattes from smallest to largest, then the VLookup function returns the correct value that the product is phenomena that you can see here. It has returned to the correct value here. Let's see the second approach. The other way to handle this situation is to use an exact match. So look at the function here. We have used the 0 here in our VLookup function argument. Even though the first column, that is the Lookup column has not been sorted here, since we are using an exact match in the function argument will look up, has returned to the correct image. So this is all about why VLookup may return an incorrect value. Let us learn about the best practices. In the next tutorial lessons that you may want to comply with were using VLookup.
14. Best Practices - Part 1: In the last lesson, we learned the way we look up sometimes returns incorrect values. In this lesson, let us discuss the best practices relating to the lookup value under table array that you may employ while working with VLookup. Let us start with the best practices relating to the lookup value. The rest server. A best practices relating to lookup value that you may use to overcome the errors and to get the desired results in VLookup. Let's see the first one. Ensure that the values you are looking up a similar left-most column, that is the first column of the data. Arrange our data table by left-most column or the first column. We do not mean column Y0. For example, here in this table, the data ranges from D22 Tj, T1, T2 are under the leftmost column is column D. If you have a data table where the lookup values or in a column other than the leftmost column of the data range. And if it is not possible to move the column to the leftmost, then you may consider using index and the Mads instead of VLookup function. Let us move on to the next two. Best practice ensures that the former top the lookup value is matching with the format of the values in the lookup column in the data table yells, you may end up with the arrows here. In this example, we are getting an error because the former top, the lookup value is in text, but the lookup column has been formatted into numbers. Let's see next, the third best practice relating to lookup value. If you are using a text as the lookup value directly inserted, the lookup function gets you to enclose that within double-quotes. Yells, you will run into an error here. As soon as you put that text in double quotes. Like this, the function returns the correct value on the error vanishes and if possible, use cell reference instead. This approach will be flexible under very obviate the need to insert courts inside of the function around the the text. With this, let us move on to discuss the best practices associated with a table, our data array. So in the case of lookup value, you may use a couple of best practices relating to the data array, our data range to get the desired results. Let's see the first one. When you copy VLookup function from one cell to other cells, lock the cell reference, making it absolute. Here in this example, we are looking up another table here, R and when the vlookup data range is not log us in this case, under a new copy these down the line, you get t errors because when you copy the formula, the cell range are the table array is not pointing to the current reference. So what you can do use sell it to the data range and then press here for to make it absolute. Then copy the VLookup function to other rows, then the VLookup will return the correct values. With this, let us discuss the second best practice. We saw that if you do not use absolute cell referencing, you may end up with errors when you copy the VLookup formula from one cell to another. So instead of using cell reference, you can also consider using named ranges in newer VLookup function argument. If you see here the data array or the data range is not absolute. And when we help copied that down the line to other rows, we are getting errors here. So instead of that data range are the celled range. You may use named reference we have already named for this particular cell ranges, sales. So if you can use that as a reference we hear, then the anchor. This gives you the same result, underwear new copy the function to other rows. You do not get errors, but you get the correct matches here, let us see the last best practice associated with the data array. So, or you referencing to the lookup values in another worksheet or our book here in this case, we are referencing to honor that worksheet in this workbook. If that is the case, make sure that you are referencing correct one. Aren't they ensure that you do not? For example, accidentally Delhi to that reference to OK shade yells, you may get some data. So you few Delhi to this worksheet here. Then the VLookup function refers to Jenn OK shade that is no longer available under throws up thaw or your fear or this is all about the best practices relating to the lookup value 100 table array that you may imply while you are working with VLookup function. Let us learn about the best practices relating to column index and range_lookup In the next lesson.
15. Best Practices - Part 2: In the last lesson, we discussed about the best practices relating to lookup value under table array that may help in working with VLookup. In this lesson, we shall discuss about the best practices and make yourself, when entering the column index number under the range_lookup argument. Let us start with the column index number. The earlier couple of best practices relating to the column index number argument in VLookup function. Let us see them in detail. Counter the column number left to right from the first column or the left-most column of the data range. Here in our example, column D, column one, column two, and so on. He enters the correct column number in the function or demand. Yells, You may not get an error, but the function may still in return and incorrect. For example, here we are trying to return the value sub-product from column two, but you may not get any error even if our input six as the column index number in the function argument. But that is not what we are looking for. Let us see the next tip is to practice. Take care that you are not entering a column index number a 0 or greater than the total number of columns in the data range yells, You will get an error. We have already discussed this in lesson 11. Look at the example here. We have a total of seven columns here in the data range. But when you enter number nine as the column index number, then the function argument is returning them or fear. Or on the other hand, if we enter less than one, that is 0, say for example, I'm entering here as 0. Then you get a value error. With this, let us discuss the best practices associated with the range lookup. You can employ a couple of best practices while entering the last argument in any VLookup function. That is the range_lookup argument, wherein you will enter true or false depending upon your requirement. Let us see the first two best-practice. Remember to use falls as the range_lookup argument for getting an exact match. That is, when you are trying to look up for a name or a product order, or a product code, et cetera. On the other hand, use clue. When you know that you need to find the approximate or closest to match, aren't. There may not be an exact match possible. This is when you are trying to return, for example, student grades based on a table of test scores or sales, given a table of sales volume, and so on, are triggers the approximate match. You already know that you can enter one or true, or just leave that argument has blank. Let us see the next two best practice for entering the range_lookup. When you use an approximate match, the answer to sort your look-up column into our sanding in case of numbers are a2 is that in case of texts to values, yells, you may end up with incorrect matches. Here in this example, we helped us Sales Quantity column of the discount table sorted in ascending underway lookup, correct values in column years for each value in column D. However, for example, if there is no sorting or if the data is sorted differently, that is from is2 EA are in descending, then the function may return unexpected results are it may result in error as well. Here, if you sort this sales quantity of the lookup table from largest to smallest, then the VLookup function throw some shadows. Let us understand the last best practice associated with the range_lookup. There could be situations wherein we have used an exact match, but VLookup is still returning on in a era. This may be because the lookup value does not exist or there are issues with the formatting of the lookup column or the lookup value, what is formatted AS number, but the other is formatted as text or vice versa. This is all about the best practices associated with column index number on the range_lookup arguments that you may need to aware of where you're working with VLookup. With this, let us move on to the next lesson to discuss about the class project.
16. Class Project: With the completion of the lessons, it's time we discussed the class project. This class project contains five exercises that will help you to reinforce the learning that you have heard so far from the various lessons. You will be using the file 0 3i Fund Master VLookup iPhone class project zealous. For the class project. The exercise tabs are lambdas, dx1, dx2, etc. These tabs contain the sample data onto the instructions to complete the exercise. The results tabs or lambdas, AX one result, results, et cetera, are under these terms contain the results up the exercise aren't aware is give you an idea as to how your result will look like when you have completed the exercises. You will also see some hints in some of these worksheets complete to their various exercises following the steps outlined in the instructions under comparable to final wrestled with the details from the options, refer back to any of the lessons. Should there be a need for clarification? With this, we have come to the end of the course. We'll look up.
17. Wrapping Up: With the completion of all these lessons, it's time to wrap up the course and discuss the next steps. By now, Yoda successfully complete or the class project. Do post your results. I'm let me know if you need any items of clarification will certainly come back on that. Also, I encourage you to create your own projects and share the same here. That will help me to provide you with the appropriate feedback. It may also help other learners are, and they may also like to share their views and suggestions. Do not stop with these lessons. Explore and practice. Provide your feedback on the course, its content, presentation, et cetera. Will happy to receive them to incorporate in my future courses. And finally, you have big thanks for selecting this course. If you feel that this course has been informative and useful, do recommend to a France and others who may want a similar cause.