VLOOKUP Function

The Excel VLOOKUP function searches for valuable data in the array's column using the lookup value. The V stands for vertical, which means exploring the data vertically. This function is part of the Lookup and Reference function. While playing with millions of data, extracting the data from a specific column is very challenging. For example, you have two datasets, Employee and Payroll, and the employee ID column is common in both datasets; you want to extract the salary details for the particular employee ID using the VLOOKUP function.

Compatibility

This advanced Excel function is compatible with the following versions of MS Excel −

Excel for Microsoft 365
Excel for Microsoft 365 for Mac
Excel for the web
Excel 2024
Excel 2024 for Mac
Excel 2021
Excel 2021 for Mac
Excel 2019
Excel 2016
Excel 2013
Excel 2010
Excel 2007

Syntax

The syntax of the VLOOKUP function is as follows −

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Arguments

You can use the following arguments with the VLOOKUP function −

Argument	Description	Required / Optional
lookup_value	It specifies a numeric value or cell reference. The lookup value must be presented in the table array's first column where the data is to be searched.	Required
table_array	It represents the cell range within which the VLOOKUP function would search for the lookup value and retrieve the resulting value.	Required
col_index_num	It specifies the column number to find the targeted value. In the table array, the leftmost column is considered 1, the second-most column is considered 2, and so on.	Required
range_lookup	It specifies the Boolean value as TRUE(Approximate match) or FALSE(Exact match). The TRUE value(by default) means the closet value in the first column is to be searched. Additionally, assume that the table_array is already sorted alphabetically/numerically. The FALSE value means the exact value in the first column of the table is to be searched.	Optional

Points to Remember

You can use the wildcard characters in the first argument lookup_value when specifying the FALSE value in the range_lookup argument, and the text string specified in the lookup_value.
The best practice using the VLOOKUP function is to sort the first column in the table_array before employing the TRUE option in the range_lookup.
To quickly fill in the formula in the cells' range, you must utilize the absolute references for the range_lookup to fill the exact lookup range.
Before evaluating the VLOOKUP function, the data must not contain trailing, leading spaces, or special characters, as the function generates inaccurate results.
If the col_index_num argument is larger than the total number of columns in the table array, the VLOOKUP function will retrieve the #REF! error.
If the first argument table_array is smaller than 1, the VLOOKUP function will return the #VALUE! error.
If the range_lookup is set to FALSE and the exact value is not found in the first column, the VLOOKUP function will retrieve the #N/A error.
If the lookup_value is less than the smallest value presented in the table array's first column, then the VLOOKUP function will return the #N/A error.
If you specify the text string in the first argument, then it must be enclosed in double quotation marks. Otherwise, will the VLOOKUP function retrieve the #NAME? error.

Example of VLOOKUP Function

Practice the following example to learn the use of the VLOOKUP function in Excel.

What does the VLOOKUP function do in Excel?

In this example, we will demonstrate the execution of the VLOOKUP function to search for specific data in the lookup column of the defined table.

Step 1 − First, assume the sample dataset where the lookup value is specified in the cell range B2:B4 and the respective data is mentioned in the cell range C2:G4.

Step 2 − After that, enter the formula =VLOOKUP(C5,B1:G3,4,FALSE) in the C7 cell.

And then hit the Enter tab to get the result.

Step 3 − Moreover, move to the G7 cell and write the formula =VLOOKUP(C5,B1:G3,11,FALSE). In this expression, the third argument, col_index_num, is 11, which does not exist in the table array.

Therefore, the VLOOKUP function returns the #REF! error.

Download Practice Sheet

You can download and use the sample data sheet to practice the VLOOKUP function.

Save or share this article