VLOOKUP in Excel (Table of contents)
Introduction to VLOOKUP Function in Excel
VLOOKUP in Excel is used to lookup the value with a reference cell and fetch the value from the selected lookup table array and is quite useful and one of the most widely used excel functions We can use a table or single column to lookup the value. And all the lookup can be done in a vertical zone or with columns only.
VLOOKUP Formula in Excel:
The vlookup function uses of the arguments:-
There are four arguments in vlookup function which is below mention:
- Lookup value (required argument) – it is the value that we want to look up for in the column of a table. Where you want or get the value from another table.
- Table array (required argument) – it is the data array that is to be searched. The vlookup function searches in the left-most column of this array. We can say that this is a matching table.
- Column index number (required argument) – an integer, specifying the column number of the supplied table array, that you want to return a value from. If you only using it for data matching then you can put 1 but if you want to get a value from another column behave on matching the lookup value then you need to put the column no from matching column no.
- Range lookup (optional argument) – it defines what this function should return in the event that it does not find an exact match to the lookup value. The argument can be set to true or false, which means:
- True – approximate match, that is, if an exact match is not found, use the closest match below the lookup value.
- False – exact match, that is, if an exact match not found then it will return an error.
- We can also use 0 for false and 1 for true matching.
Steps for Using VLOOKUP Function
- Click on formula tab > lookup & reference > click on vlookup.
- Also, click on the function icon then manually write and search the formula.
- We get a new function window showing in the below mention pictures.
- Then we have to enter the details as shown in the picture.
- Put the lookup value where you want to match from one table to another table value.
- You need to put the table array which is another table value.
- Put the col index number for another table vertical value which is a need.
- Rage lookup false for exact match and true for an approximate match.
- You can also use 0 for an exact match and 1 for an approximate match.
Shortcut for using the Formula
Click on a cell where you want to result in value then put the formula as below mention
= vlookup (lookup value, table range, column index) > enter
Explanation for VLOOKUP Function:
This function helps you to locate specific information in your spreadsheet. When the user uses the vlookup function for finding specific information in an MS Excel spreadsheet, each matching information is displayed in the same row but in the next column. This function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index number position.
We can also use one sheet to another sheet and one workbook to another workbook also
How to Use VLOOKUP in Excel?
Vlookup function is very simple and easy to use. Let us understand the working of vlookup. Below mention are the details using the formulas.
Example #1 – Exact Match
To search for an exact match, you put false in the last argument.
As above mention in table b there is all information of the employee like department, employee id, address mobile no, etc. You can suppose as array table or master table data. And you have another table where required only contact no of the employee. So as the employee name is a unique column then the employee name a lookup value in a table where you want to get a result.
Master data table b as above showing is a table array, and in master data, you can see that mobile no column is on the 5 number column index. Then we need to put the 0 for exact matching or 1 for false matching.
You can see the result here:
Formula: – “=vlookup(a21,a1:e12,5,0)”
We can see that in a table all values are matching the exact value.
Example #2 – Approximate Match
As above showing in the picture in the H column, there is employee age mention and column I employee name. You can take this as an array or master table. Now I want to put the age value in k2 then we get the employee name which is approximate age as given in k2 age value as showing below mention pictures.
So lets we check the formula now.
“=vlookup(k2,h2:i12,2,true)”
Then we can get the approximate matching value.
As you see, the formula returns Mr. Puneet Sharma whose age is 43, while we also have Mr. Manish patial that age 48 but 43 is much closer to 44 than 48. So, why does it return Mr. Puneet? Because vlookup with approximate match retrieves the closest value that is less than the lookup value.
Things to Remember
- The vlookup function returns result in any data type such as a string, numeric, date, etc.
- If you specify false for the approximate match parameter and no exact match is found, then the vlookup function will return #n/a.
- If you specify true for the approximate match parameter and no exact match is found, then the next smaller value is returned.
- If the index number is less than 1, the vlookup function will return the #value!.
If the index number is greater than the number of columns in the table, the vlookup function will return #ref!
Recommended Articles
This has been a guide to VLOOKUP Function. Here we discuss the VLOOKUP Formula and how to use the VLOOKUP function along with an excel example and downloadable excel templates. You may also look at these useful functions in excel-
