VLOOKUP

Q: Should I use VLOOKUP or XLOOKUP?

[XLOOKUP](/excel-functions/xlookup) is the modern replacement for VLOOKUP with more features and flexibility. Use XLOOKUP if you have Excel 365 or Excel 2021+. VLOOKUP still works fine for basic lookups and is available in all Excel versions.

Search for a value in the first column of a table and retrieve data from another column in the same row.

Lookup & Reference

Excel All versions

Google Sheets Supported

Spreadsheet editor

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) Returns: Varies

Arguments

Argument	Required	Description
lookup_value	Yes	The value to search for in the first column of the range.
table_array	Yes	The table of data in which to search for the lookup value.
col_index_num	Yes	The column number in the table from which to retrieve the value.
range_lookup	No	If TRUE or omitted, it finds an approximate match. If FALSE, it looks for an exact match.

About

VLOOKUP finds a value in the leftmost column of your data table and returns information from another column in the same row. Use it when you need to pull matching data from large tables, like finding an employee's salary from their ID number or looking up a product price from its code.

The function works in exact or approximate match mode. Exact matching (FALSE for the last parameter) is most common and safest for finding specific values. Approximate matching (TRUE) requires your data to be sorted and returns the closest match, useful for tax brackets or shipping rates.

For more flexible lookups, check out XLOOKUP, which can search any column and returns arrays. You can also combine INDEX with MATCH for more control over your lookups. If your data is organized horizontally instead of vertically, use HLOOKUP.

Examples

Product lookup with exact text matching

See how VLOOKUP handles text matching. Try changing the lookup value or add a trailing space - watch how even tiny differences cause #N/A errors.

Spreadsheet editor

Column index errors

Watch what happens when the column index exceeds your table range. The last formula requests column 5 but the table only has 3 columns, causing a #REF! error.

Spreadsheet editor

Tax brackets with approximate match

Use VLOOKUP with TRUE to find tax brackets. Change the income value and watch it automatically select the correct bracket. Perfect for tiered rates and ranges.

Spreadsheet editor

Watch out for

⚠

Lookup value not in first column

VLOOKUP can only search the leftmost column of your table. If your lookup value is in column B and you want to return from column A, VLOOKUP won't work.

→ Rearrange your data so the lookup column is first, or use XLOOKUP which can search any column and return from any direction.

⚠

Wrong column number

Column numbers start at 1 for the first column in your range, not the spreadsheet column. Using the wrong number returns data from the wrong column or causes a #REF! error.

→ Count columns within your table_array range starting at 1. If your range is C2:F100, column C is 1, D is 2, E is 3, and F is 4.

⚠

#N/A errors from extra spaces

Lookup values fail when there are extra spaces before or after text, even though the values look identical.

→ Use TRIM on your lookup value or source data to remove extra spaces. Or wrap your lookup value: =VLOOKUP(TRIM(A2), table, 2, FALSE).

⚠

Approximate match with unsorted data

Using TRUE for range_lookup on unsorted data returns incorrect results without warning.

→ Either sort your data in ascending order by the first column, or use FALSE for exact matching. Most lookups should use FALSE unless you specifically need approximate matching for ranges.

Tips & notes

The lookup value must always be in the leftmost column of your table range. VLOOKUP cannot search to the left. Column numbers are relative to your table_array, not the worksheet. The first column in your range is always column 1. Use FALSE for the range_lookup parameter in most cases. TRUE requires sorted data and is mainly useful for tax brackets, grade scales, or shipping rates. If you get a #REF! error, your column number is larger than the number of columns in your table range.

Common questions

Should I use VLOOKUP or XLOOKUP?

XLOOKUP is the modern replacement for VLOOKUP with more features and flexibility. Use XLOOKUP if you have Excel 365 or Excel 2021+. VLOOKUP still works fine for basic lookups and is available in all Excel versions.

Why does VLOOKUP return #N/A?

VLOOKUP returns #N/A when it can't find your lookup value. Common causes include typos, extra spaces, the lookup value not being in the first column, or using FALSE for range_lookup when you need an approximate match. Check your data carefully and try using TRIM to remove extra spaces.

Can VLOOKUP look up values to the left?

No, VLOOKUP can only return values from columns to the right of the lookup column. If you need to search one column and return from a column to its left, use XLOOKUP or combine INDEX with MATCH instead.

When should I use TRUE vs FALSE for range_lookup?

Use FALSE (exact match) for most lookups like product codes, employee IDs, or customer names. Use TRUE (approximate match) only when working with sorted numeric ranges like tax brackets, commission tiers, or grade cutoffs. Always sort your data in ascending order when using TRUE.