ExcelLookupBeginner

VLOOKUP

Searches for a value in the first column of a table and returns a value in the same row from another column

Read the syntaxReview worked examplesOpen the spreadsheet app
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

Searches for a value in the first column of a table and returns a value in the same row from another column

VLOOKUP (Vertical Lookup) searches for a value in the leftmost column of a specified table array and returns a value from a corresponding row in a user-defined column index. To ensure accuracy, users should typically set the [ookup] argument to FALSE (or 0) for an exact match. If set to TRUE (or 1), the function performs an approximate match, which requires the first column of the table to be sorted in ascending order. Key technical limitations include the inability to look to the left of the reference column and the 'brittleness' of the column index number, which can break if new columns are inserted into the table range. While historically paired with IFERROR to handle missing data, note that XLOOKUP has a built-in [ound] argument as a modern alternative that avoids nested function complexity.

Quick reference

Syntax

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

Inputs

Arguments

lookup_valueThe value to find
Example: A2
table_arrayThe table to search in
Example: A:D
col_index_numColumn number to return (1=first column)
Example: 3
range_lookup (optional)TRUE=approximate match, FALSE=exact match
Example: FALSE

Formula patterns

Examples

1

Exact match for employee ID

=VLOOKUP(A2, D2:F100, 3, FALSE)
hrexact match
2

Approximate match for tax brackets

=VLOOKUP(B2, G2:H10, 2, TRUE)
financeapproximate match
3

Lookup with error handling

=IFERROR(VLOOKUP(A10, C2:E50, 2, 0), "Value not found")
data validationerror handling

Avoid these issues

Common Errors

1

#N/A

Cause: The lookup value does not exist in the first column of the range, or there is a data type mismatch (e.g., searching for a numeric 101 when the table contains text '101').

Fix: Check for trailing spaces using TRIM or ensure both the lookup value and the source table use the same data format (Text vs Number).

2

#REF!

Cause: The col_index_num provided is greater than the total number of columns in the table_array.

Fix: Verify the column count in your range and ensure the index number refers to a column actually included in the selection (e.g., if the range is A:B, the index cannot be 3).

3

#VALUE!

Cause: The col_index_num is less than 1.

Fix: Ensure the column index is a positive integer representing the relative position of the data you wish to retrieve.

Platform support

Compatibility

Excel 2007+Excel-first

Source: Microsoft Support

Common questions

Frequently Asked Questions

Searches for a value in the first column of a table and returns a value in the same row from another column

alue: The value to find rray: The table to search in um: Column number to return (1=first column) ookup: TRUE=approximate match, FALSE=exact match

#N/A: Check for trailing spaces using TRIM or ensure both the lookup value and the source table use the same data format (Text vs Number). #REF!: Verify the column count in your range and ensure the index number refers to a column actually included in the selection (e.g., if the range is A:B, the index cannot be 3). #VALUE!: Ensure the column index is a positive integer representing the relative position of the data you wish to retrieve.