Excel VLOOKUP Tutorial (Table of contents)
VLOOKUP Tutorial in Excel
VLOOKUP is an in-built function in MS Excel. A user can look up and get the data from a column of a table or sheet with the help of VLOOKUP Excel function.
A user can look up for an exact match or a part of the value (approx. match). Here ‘V’ stands for Vertical in VLOOKUP, which means it will work on the only column. VLOOKUP function is very popular in MS Excel.
Syntax of VLOOKUP Formula
Below is the syntax of the VLOOKUP formula:
Arguments of VLOOKUP Function:
- lookup_value: It is a mandatory field, provides the value which the user wants to search in the column or range. If the value is not found in the range or table array, then it will return the ‘#N/A’ error.
- table_array: It is a mandatory field, the range where a user wants to look up the value. It should contain all the value of the Col_index_num.
- col_index_num: It is a mandatory field, the column number in the table_array from the first column of table_array. It will return ‘#REF!’ error if the number of column_index_num is greater than the number of columns in table_array
- range_lookup: It is an optional field, by default it will take as an exact match. it has two option either true or false.
- FALSE – It will check for the exact match in the range. For this provide value as 1. In this, the column must be sorted numerically or alphabetically.
- TRUE – It will check for the approximate match in the range. For this provide value as 0.
How to Use VLOOKUP Tutorial in Excel?
Excel VLOOKUP Tutorial is very simple and easy. Let’s understand How to Use VLOOKUP Tutorial in Exel with some examples.
Excel VLOOKUP Tutorial – Example #1
How to look up for the Approximate Match Value in the Table.
There is a company called ‘ABC Consultancy Services’ which has employee details like First Name, Last Name, City and Skills.
Le’s consider if a user wants to find the approximate match as the Skill ‘Python’ in the employee details of the company ‘ABC Consultancy Services’. So how to get the required Skill Employee First name.
Open MS Excel, go to Example #1 sheet where employees details are kept by the company.
As the user wants to find the Required Skill, Employee, and First name. So apply the VLOOKUP formula.
4.9 (5,803 ratings)
View Course
- As mentioned in above-required skill is Python, so lookup value is Python which is in cell G3. lookup_value = G3
- Now provide the input for the table array which is A3 to D15. table_array: A3: D15
- Now provide the input for the column index which is 1 because the user wants to find the first name of the required skill. col_index_num: 1
- Now provide the input for the range lookup which is TRUE because the user wants to find the approximate match of the lookup value. range_lookup: TRUE
- Now press the Enter button, so VLOOKUP formula in the table is applied, which is =VLOOKUP (G3, A3:D15, 1, TRUE)
Summary of Example #1:
As the user wanted to find the required skill employee first name from the employee details table, and the result is Jordan because of the VLOOKUP formula.
Now if he wants to find any other skill then just need to change the VLOOKUP value which is G3 in Example #1 sheet and he will get the right answer for his problem.
Excel VLOOKUP Tutorial – Example #2
How to look up for the Exact Match Value in the Table.
There is a company called ‘ABC Consultancy Services’ which has employee details like First Name, Last Name, City and Skills.
Let us consider if a user wants to find the Exact match as the required Last Name of Jenifer in the employee details of the company ‘ABC Consultancy Services’. So how to get the required Last Name of Jenifer with the exact match.
Open MS Excel, go to Example #2 sheet where employee details kept by the company.
As user wants to find the last name of Employee Jenifer, so apply VLOOKUP formula.
- As mentioned in the required Last name of Jenifer, lookup value is Jenifer which is in G3. lookup_value = G5
- Now provide the input for the table array which is A3 to D15. table_array: A3: D15
- Now provide the input for the column index which is 2 because the user wants to find the Last Name of the required skill. Which is available in the 2nd column of the table array. col_index_num: 2
- Now provide the input for the range lookup which is FALSE because the user wants to find the Exact match of the lookup value. range_lookup: FALSE
- Now press the Enter button, so VLOOKUP formula in the table is applied, which is =VLOOKUP (G3, A3:D15, 2, FALSE)
Summary of Example #2:
As the user wanted to find the required Last Name of the employee first name which is Jenifer from the employee details table, as the result Lenger can be seen which is because of the VLOOKUP formula.
Now if he wants to find any other Employee’s last name then just need to change the VLOOKUP value which is G3 in Example #2 and he will get the right answer for his problem.
Things to Remember
- If the number of col_index_num is greater than the number of columns in table_array, then it will return ‘#REF!’ error.
- If the VLOOKUP value is not found in the range or table array, then it will return the ‘#N/A’ error.
- If a user is not providing the value for Range_lookup then by default it will take as TRUE. which will be an approximate match.
- In the Range_lookup the table data should be sorted in an alphabetic or numeric way. It will return the closest match.
- If a user wants to give cell no or that value itself, he can give in the VLOOKUP value argument.
Recommended Articles
This has been a guide to VLOOKUP Tutorial in Excel. Here we discussed how to use VLOOKUP tutorial in Excel along with practical examples and downloadable excel template. You may also look at the following articles to learn more-
