![\"vlookup](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/05\/vlookup-apprx-match.png\")
<\/a><\/p>\n<\/p>\n
In the above sample, we can see that the table is already arranged in ascending order.<\/p>\n
Now, we are going to fetch the approximate value for a student who scored 45%. This approximate match could be very useful because not every student could get 30, 50, 60, 80, or 90 percent marks. There can be fluctuations in it.<\/p>\n
So, in order to decide on those fluctuated percentages, we can use vlookup with an approximate match.<\/p>\n
This approximate match works even when the lookup value doesn\u2019t match the table lookup value. If the lookup value is the mid of some two values, then it fetches the value with the smallest range among it.<\/p>\n
The approximate match is also known as the closest match as they fetch the closest value for the lookup value.<\/p>\n
In the above sample, we have looked up for the value of 45% and since 45 is not exactly available in the table, then the lookup value sees where this value lies.<\/p>\n
As 45 lies between 30 and 50, the credit corresponding to 30 will be updated in the vlookup value cell. This is how the approximate match works.<\/p>\n
If there is no provision of range lookup, then the parameter itself considers it to be as 1.<\/p>\n
Let\u2019s see a sample where we don\u2019t give the range lookup parameter in the function:<\/p>\n
In the above sample, look at the function in the formula bar.<\/p>\n In the function, there are only 3 parameters that are being mentioned and the last parameter is not given.<\/p>\n But still, the vlookup function performs the action without throwing an error. This is because the default value of range lookup is 1 and if we don\u2019t mention it in the parameter, then the function considers it to be as 1 and starts performing an approximate match.<\/p>\n For the above sample, we have looked up for the 75 percentage, and though we haven’t entered the range lookup, it went on with the approximate match and fetched the value as 5 in the D9 cell.<\/p>\n In the exact match, the lookup value we have entered is being searched in the table and it returns the value only when there is an exact match.<\/p>\n Let’s look at a sample:<\/p>\n In the above sample, we have entered the range lookup value as 0 which represents to lookup for the exact value match. The table range is from A2:D25 and the column index provided is 2 which means we are looking for maths marks. Let’s see how to implement vlookup in MS-Excel:<\/p>\n In the above sample, we have a list of students with their maths, physics, chemistry marks. Now, in order to search each and every value, we can just use the vlookup function to lookup a mark. This is what is being performed in the G5 cell.<\/p>\n Using vlookup function, we have fetched the Nithish’s chemistry mark in it. In the function, we have specified the name cell value as the lookup value.<\/p>\n A1:D25 denotes the table array. After writing the function completely, Nitish\u2019s chemistry mark is being taken from the table and it is shown in the G5 cell.<\/p>\n There is also another way to make use of this function. You can either directly write the formula or if you are not sure about the formula, then follow the steps:<\/p>\n 1: Click on the Formulas tab.<\/p>\n 2: Choose the insert function.<\/p>\n 3: A box appears with the list of functions. Hence, you can search for the vlookup in it.<\/p>\n 4: Select the vlookup from the list.<\/p>\n Now, when we clicked on the vlookup function, a box with its function arguments popped up.<\/p>\n 5: Fill the details as per you are looking for.<\/p>\n 6: Press ok.<\/p>\n Let’s look at a sample.<\/p>\n In this sample, we are trying to fetch the math\u2019s marks of Arya from the table. To do so, we have filled the function arguments in the box. In the lookup value we have entered it as A12 as arya name appears there.<\/p>\n Here in the table array, we have filled it with the table range i.e., from A1:D25. In the col_index_num, enter the maths column number i.e., 2 in this table. In the range_lookup, fill it as 0 as we are looking out for an exact match.<\/p>\n Now, after entering the values and once we press the ok button, the value is being fetched from the table and shown in the G6 cell.<\/p>\n Let’s see how data validation can help us in vlookup function. From the data validation, we are going to use the list option from it.<\/p>\n Enter the source as which should appear as the list from the table. Now, apply the vlookup formula.<\/p>\n We use this data validation list option because it helps in saving our time by not typing the lookup value. Just a click on the list would fetch the required value from the table using vlookup function.<\/p>\n Let’s look at a sample.<\/p>\n So, here we have made the list as names using data validation and we have used the vlookup function to lookup for the marks.<\/p>\n Now using the same list we will know whether the marks change if we change the name chosen in the list.<\/p>\n For that, let\u2019s look into a sample where we have chosen the different name now:<\/p>\n From the above sample, we can see that the vlookup value is being changed as the name changes.<\/p>\n Now you may wonder how this is possible. To understand it, have a keen look at the formula in the formula bar. We have written the formula as \u2018=VLOOKUP(F2,A2:D25,3,0)\u2019.<\/p>\n Here, we have mentioned the lookup value as the options we choose and that’s why it changes the marks accordingly.<\/p>\n Let’s look at how vlookup function works with formula. It automatically updates with the value when the vlookup value changes.<\/p>\n For clear understanding, let’s look at a sample:<\/p>\n In the above sample, we are adding 10 marks extra to the maths marks we look up for.<\/p>\n Now, we have looked up Akash Maths marks in G3 cell and we have added 10 marks with it. The result of the sum value is in the I3 cell.<\/p>\n Now, even when we change the name in the lookup value, then for that lookup value also, 10 marks will be added on in the I3 cell.<\/p>\n Let’s have a look at it also.<\/p>\n When we changed the lookup value as Thulya, then the maths marks of thulya is being fetched and bonus marks of 10 is added. This is being displayed in the I3 cell. The value automatically gets updated because it does not depend on the values. It depends on the cell number we have entered to calculate in it.<\/p>\n This is one type of vlookup. In the first match of vlookup, it matches with the first value it sees with the exact match. This applies when we have duplicate data in the table range.<\/p>\n Let’s look at a sample:<\/p>\n From the above sample, we could see 4 data rows in the spreadsheet with the name \u2018Nithish\u2019. Wildcard match is a match that works with a partial match in the search on a lookup value. But in order to do partial matching, you have to enter the range lookup as 0 to do an exact match.<\/p>\n Let’s look at a sample for clear understanding:<\/p>\n In the above sample, we have entered the formula as =VLOOKUP(\u201c????\u201d,A2:D28,1,0). This \u201c????\u201d represents that the value we are looking up is the 4 letters name of student.<\/p>\n Until now, we have seen how a vlookup works when the column index is being provided. But now, let\u2019s have a look at the sample where the column index is replaced by the match parameter.<\/p>\n In this sample, we are going to look for the student whose name is Arjun and whose maths marks is 22.<\/p>\n Look at the formula applied in the cell G3 in the formula bar. Here, we haven\u2019t mentioned the column index. Instead, we will use a match function.<\/span><\/p>\n Here, we have written the formula as<\/span> =VLOOKUP(F2,A2:D6,MATCH(F3,A1:D1,0),0)<\/b><\/p>\n F2 denotes which value we are looking for i.e., Arjun here.<\/span><\/p>\n A2:D6 denotes the table range.<\/span><\/p>\n MATCH(F3,A1:D1,0) – This denotes that the column index name is Maths which we are looking for in the F3 cell.<\/span><\/p>\n A1:D1 – denotes that these are column index headers.<\/span><\/p>\n 0 here represents to make an exact match in the column header.<\/span><\/p>\n The 0 after the match function also denotes to make an exact match for the whole vlookup function.<\/span><\/p>\n In this function, there is no constraint that we have to provide only one lookup value. Instead, we could add multiple criteria using the \u201c&\u201d symbol in between. Now, let’s have a look at a sample:<\/p>\n In this sample, we are fetching Akash\u2019s chemistry marks providing his name and math marks as the lookup value. In this dataset, we have two students with the name Akash. So, we are providing the maths marks also to fetch the exact student we are looking for.<\/p>\n \u201cIf vlookup\u201d function helps in calculating and comparing the values that we get by the vlookup. Let\u2019s now look at a sample of how the if function works along with vlookup function.<\/p>\n<\/a><\/p>\nExact match in Vlookup<\/h3>\n
<\/a><\/p>\n
\nHere, the lookup value is Tarani.<\/p>\n
\nThen, the 0 represents the exact match from the table.
\nAs we have provided the parameters, the maths mark is being fetched from the table that appears in the G3 cell.<\/p>\nHow to use Vlookup in Excel?<\/h3>\n
Method 1<\/h4>\n
<\/a><\/p>\n
\n4 represents the column number from where the value should be fetched.
\n0 represents the exact match.<\/p>\nMethod 2<\/h4>\n
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nVlookup function with Data validation<\/h3>\n
To utilize the data validation list option<\/h3>\n
\n
<\/a><\/p>\n<\/a><\/p>\nVlookup with formula<\/h3>\n
<\/a><\/p>\n<\/a><\/p>\nFirst Match in Excel<\/h3>\n
<\/a><\/p>\n
\nNow, when we look up the Nithish\u2019s Maths marks then it fetches the data from the cell B5 since its first while searching.<\/p>\nWildcard Match in Excel<\/h3>\n
<\/a><\/p>\nTwo-way VLookup in Excel<\/h3>\n
<\/a><\/p>\n<\/a><\/p>\nVLOOKUP providing multiple criteria<\/b><\/h3>\n
<\/a><\/p>\nIF VLOOKUP in Excel<\/h3>\n