What is VLOOKUP in Excel?
VLOOKUP Function in EXCEL stands for Vertical Lookup. It is an Excel function to find specific information in a vertical pattern across a table or Excel spreadsheet.
The below image shows the Lookup column and Result column that Vlookup will use to scan and display the result.
For example: Consider a database with an employee ID, name, work department, and location. Now, you would like to check the work location of the employee with employee ID 12. Rather than doing it manually, you can apply the VLOOKUP formula to automate the process.
You can do the modus operandi to find the requisite by inserting the formula:
Cell A3 denotes the employee ID 12, the table array denotes the selected range in Red, and the column number indicates the selected column 4 for finding the address of the employee-Danny, against the employee name. Once you enter the Vlookup formula in the cell, hit the Enter key, and you will get the exact match.
The image below shows the output, i.e., the address of Danny- Detroit.
Key Highlights
- In Vlookup Function in Excel, the V stands for vertical. The Vlookup function scans from the left and moves to the right of the data table.
- Vlookup scans for both exact and approximate matches of the looked-up value.
- When duplicate values are non-existent, we use them for a given data table, and if Excel finds duplication, the function returns only the first one.
- True implies an absolute, and false indicates an incorrect or close match.
VLOOKUP Syntax
Vlookup Arguments
There are four arguments in the Excel vlookup function, mentioned below:
- Lookup value (required): When we need to work with an element, Excel indicates it as the lookup value. For example, the employee ID.
- table_array (required): It is the range containing the lookup value. For example, the data range has the employee ID, name, work department, and address.
- Col_ index_ num (required): When we wish to return a value or element from a table array, col_index_num specifies its column number. For example, The column number for finding the address is 4.
- range_lookup (optional): It is the fourth argument of the Vlookup formula. Range_lookup instructs Excel to return either an Exact or Approximate If we provide 0 or False, it will return an Exact match, and if we provide 1 or True, it will return an Approximate match to the looked-up value.
How to use the VLOOKUP function in Excel?
Let us understand the use of the Vlookup function with examples:
Example #1: Finding the Exact Match
#1: The table below shows the Employee IDs and the Names, departments, and Addresses of company employees. We wish to find the Department of Persons with Employee ID 16.
Solution:
Follow the below steps to find the Department of Employee ID 16
Step 1: Organize your data in a table because the Excel function will automate its operations in the same manner
Step 2: Click on the cell G4 and Enter the lookup formula: =VLOOKUP(F4,A2:C12,3,FALSE)
In the above formula, F4 denotes employee ID, the table array is the highlighted range, and the column is an integer you select to get the looked-up value. The last value helps in returning the exact or approximate match.
- If True, it is an approximate match.
- If False, it is an exact match.
Below given image shows the entire Vlookup operation:
Step 4: Press Enter to see the result as shown in the below image.
Example #2: Finding Another Exact Match
The following table shows students and their marks obtained out of 100 in English and Maths. We need to find scored marks of Emma in the English subject.
Solution:
Follow these steps to find marks of Emma in English.
Step 1: Organize your data from left to right
Step 2: Place the cursor in the G2 cell and enter the below-given formula,
=VLOOKUP(A6,A2:D12,3,0)
Note: Here, we can also use 0 instead of False to give the exact match
Step 3: Once you hit the Enter key, it will display the output, i.e., marks obtained in English by Emma, as shown in cell G3
Example #3: Finding an Approximate Match
In this example, we wish to find the Department of Employee ID 22
Solution:
Follow the steps given below.
As per the data, we do not have the Employee ID of 22. So, we have to automate the process through an approximate match function.
The entire step is the same as the exact match function except for the lookup_range value.
Below are images to understand the same.
In the range_lookup function, we provide TRUE for the approximate match.
Once you press Enter key, Excel gives the output Admin. If the looked-up data is not in the table, Excel returns the relative value (approximate) to the previous value.
Explanation– The table does not have data about Employee ID 22. Thus, Excel will return the ID value just before ID 22, i.e., ID 21. The Department of Employee with ID 21 is Admin; hence the result for Employee ID 22 is also Admin.
Excel Vlookup Errors
- #N/A!: Excel gives this error when the Vlookup function cannot find lookup_value in the given table_array.
- #REF!: This error occurs when we provide a column number (col_index_ num) more than the number of columns in the data table (table_array).
- #VALUE!: Excel gives this error when we do not provide the column number (col_index_ num) or we have given a value less than 1.
Things to remember
- #N/A!: Excel gives this error when the Vlookup function cannot find lookup_value in the given table_array.
- Range_lookup: It instructs Excel to do a range lookup when the fourth argument is True or if we have not provided it. We use the value 0 or False to find an exact match and 1 or True for an approximate match to the looked-up value.
Frequently Asked Questions (FAQs)
Q1. How to use the VLOOKUP function in excel to find a partial match?
Answer: To use the VLOOKUP function for finding a partial match, we have to combine the wildcard character (*) asterisk with the lookup_value as shown below-
=VLOOKUP(value&”*”,data,2,FALSE)
Q2. How can the VLOOKUP Excel formula be applied for absolute and relative cell references?
Answer: When working with Excel sheets, we often want the lookup range to remain the same. To lock the range, we use absolute references by inserting dollar $ before each part of the reference as shown below-
=VLOOKUP(C3,$J$3:$L$11,2,true)
Excel uses relative cell reference when we apply the same formula to more cells. We copy the formula and paste it into the desired cell. Here, Excel automatically changes the values of lookup_value and table_array as per the original formula.
For example,
=VLOOKUP(F4,A2:C12,3,0) is the original formula
=VLOOKUP(F5,A3:C13,3,0) is the copied formula. Here Excel has changed the lookup_value and table_array as per the looked-up value.
Q3. What is the importance of the VLOOKUP function?
Answer: We use the VLOOKUP function to find specific information from a large data set. VLOOKUP works like a phone book, where you find the phone number of a person using their names in the phone book. When we have to work on large data sets, Vlookup is a time-saving tool.
Recommended Articles
The above article is EDUCBA’s guide on using the Vlookup function in Excel. Learn more by visiting these recommended articles.
23 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion
4.9
View Course
Related Courses
