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 VLOOKUP Excel function’s help.
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 values 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 the ‘#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 options, 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 a value of 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.
A company called ‘ABC Consultancy Services’ 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 company ‘ABC Consultancy Services’ employee details. So how to get the required Skill Employee First name.
Open MS Excel, go to Example #1 sheet where the company keeps employee’s details.
As the user wants to find the Required Skill, Employee, and First name. So apply the VLOOKUP formula.
4.9 (8,441 ratings)
View Course
- As mentioned in Python’s above-required skill, the 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 column index input, 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 the 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.
If he wants to find any other skill, he just needs to change the VLOOKUP value, which is G3, in the 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.
A company called ‘ABC Consultancy Services’ 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 are kept by the company.
As the user wants to find the last name of Employee Jenifer, so apply the VLOOKUP formula.
- As mentioned in the required Last name of Jenifer, the lookup value is Jenifer, who 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 column index input, 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 the 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’s first name, which is Jenifer, Lenger can be seen from the employee details table because of the VLOOKUP formula.
If he wants to find any other Employee’s last name, he just needs to change the VLOOKUP value, G3 in Example #2, and 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 the ‘#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 using the VLOOKUP tutorial in Excel and practical examples and a downloadable excel template. You may also look at the following articles to learn more-
