AI Excel Generator
← ALL GUIDES

How to Use VLOOKUP in Excel: Syntax, Examples, and Common Fixes

Updated November 4, 2026 · AI Excel Generator

VLOOKUP searches the first column of a table for a value and returns related information from another column in the same row.

This guide explains how to build the formula, choose the right match type, and fix problems that can make a lookup return the wrong result.

What does VLOOKUP do?

VLOOKUP means “vertical lookup.” It looks down the leftmost column of a range for a search value, then returns a value from a specified column to its right.

For example, you can look up a product ID in one column and return its price from another.

VLOOKUP works when the lookup column is the first column in the selected table range.

It cannot return a value from a column to the left of that lookup column.

If you need that flexibility, consider XLOOKUP or INDEX and MATCH.

VLOOKUP syntax and arguments

The function has four arguments:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: The value to find, such as a product ID in A2.
  • table_array: The range containing the lookup column and the result column, such as $A$2:$C$100.
  • col_index_num: The position of the return column within the selected range.

    The first column is 1, the second is 2, and so on.
  • range_lookup: Optional match setting.

    Use FALSE for an exact match or TRUE for an approximate match.

    If omitted, Excel uses approximate matching.

For most everyday lookups, specify FALSE.

Leaving the final argument out can produce an unexpected result when the first column is not sorted.

How to use VLOOKUP for an exact match

Suppose a worksheet has product IDs in column A, product names in column B, and prices in column C.

The data is in rows 2 through 5:

A2: P-101   B2: Desk Lamp   C2: 24.99
A3: P-102   B3: Notebook    C3: 4.50
A4: P-103   B4: Backpack    C4: 39.00
A5: P-104   B5: Water Bottle C5: 12.00

To find the price for the product ID entered in E2, use:

=VLOOKUP(E2,$A$2:$C$5,3,FALSE)

If E2 contains P-103, the formula returns 39.00.

The table range starts in column A, so column C is the third column within that range.

The dollar signs keep the range fixed if you copy the formula to other rows.

To return the product name instead, change the column index to 2:

=VLOOKUP(E2,$A$2:$C$5,2,FALSE)

How to use VLOOKUP with a cell reference

Rather than typing the search term into the formula, put it in a worksheet cell.

For example, if F2 contains a customer number and the customer table is in A2:D200, this formula returns the value from the table’s fourth column:

=VLOOKUP(F2,$A$2:$D$200,4,FALSE)

This setup is useful for a lookup form: change the value in F2 to retrieve a different record.

Keep the lookup column at the left edge of the table range, and count the return column from that edge rather than from the worksheet’s column A.

Approximate matches and sorted ranges

Use TRUE when the first column contains sorted thresholds and you want the closest value that is less than or equal to the lookup value.

For example, a commission table might list minimum sales amounts in ascending order in A2:A5 and commission rates in B2:B5:

A2: 0     B2: 2%
A3: 1000  B3: 3%
A4: 5000  B4: 5%
A5: 10000 B5: 7%

For sales of 6200 in D2, use:

=VLOOKUP(D2,$A$2:$B$5,2,TRUE)

The matching threshold is 5000, so the result is 5%.

Approximate matching assumes the first column is sorted in ascending order.

If it is not, the result may be incorrect.

For an exact match on a code, name, or ID, use FALSE.

Common VLOOKUP errors and how to fix them

  • #N/A: The lookup value was not found.

    Check for extra spaces, spelling differences, or numbers stored as text in one part of the data but as numbers in another.

    Confirm the lookup value appears in the first column of the selected range.
  • #REF!: The column index is larger than the number of columns in the table range.

    For $A$2:$C$5, valid column indexes are 1, 2, and 3.
  • #VALUE!: The column index may be less than 1 or an argument may have an invalid value.

    Check that the third argument is a positive whole number.
  • Wrong result: Check whether the final argument is missing or set to TRUE.

    Use FALSE for exact matching.

    Also verify that the column index counts from the first column of the selected range.
  • Formula changes when copied: Lock the table range with dollar signs, as in $A$2:$C$100.

    Keep the lookup cell relative if each row should search for a different value.

If a result seems to match despite extra spaces, clean the source values with Excel’s TRIM function or inspect whether the cells contain hidden characters.

VLOOKUP does not treat uppercase and lowercase letters as different in standard lookups.

VLOOKUP compatibility and alternatives

VLOOKUP is available in widely used desktop versions of Excel, including Excel 2016, Excel 2019, Excel 2021, Excel 2024, and Microsoft 365, as well as Excel for the web.

It is also available in Google Sheets, though some spreadsheet settings may use semicolons instead of commas as formula separators.

In Microsoft 365 and newer Excel versions, XLOOKUP can search in either direction and uses exact matching by default.

In older Excel versions, INDEX and MATCH can return values from columns to the left as well as the right.

VLOOKUP remains useful when the lookup column is first in the table and a compact, familiar formula is all you need.

Quick checklist before using the formula

  • Start the table range with the column containing the lookup values.
  • Count the return column from the left edge of that range.
  • Use FALSE for an exact match.
  • Sort the first column in ascending order before using TRUE.
  • Lock the table range when copying the formula down.