VLOOKUP
FunctionSearches for a value in the first column of a range and returns a value in the same row from another column
Interactive example
Spreadsheet editor
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Arguments
| Argument | Required | Description |
|---|---|---|
| lookup_value | Yes | The value to search for in the first column of the table |
| table_array | Yes | The range of cells containing the data to search |
| col_index_num | Yes | The column number in the table from which to return a value |
| range_lookup | No | TRUE for approximate match, FALSE for exact match (default is TRUE) |
About
VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a corresponding value from another column in the same row. It's one of Excel's most powerful lookup functions, commonly used for data analysis, creating reports, and linking data from different sources.
Examples
=VLOOKUP("Apple",A1:C10,2,FALSE) looks for "Apple" in column A and returns the corresponding value from column B
=VLOOKUP(101,A1:D100,3,FALSE) searches for the value 101 and returns the value from the 3rd column
=VLOOKUP(A1,Products!$A:$D,4,0) uses a cell reference for lookup value and searches in another sheet Notes
VLOOKUP can only search to the right - the lookup column must be the leftmost column of your range. For more flexibility, consider using INDEX and MATCH together, or the newer XLOOKUP function if available. Always use FALSE for exact match unless you specifically need approximate matching.
Common questions
Why am I getting #N/A error?
The #N/A error occurs when VLOOKUP can't find the lookup value. Check for exact spelling, extra spaces, or use IFERROR to handle missing values gracefully.
Can VLOOKUP search to the left?
No, VLOOKUP can only return values from columns to the right of the lookup column. Use INDEX and MATCH or XLOOKUP for left lookups.
What's the difference between TRUE and FALSE for range_lookup?
FALSE (or 0) finds exact matches only. TRUE (or 1) finds approximate matches and requires sorted data. For most cases, use FALSE for exact matches.