Excel VLOOKUP Return Value: How It Works & Fixes

Formulas & functions · Updated November 22, 2026

In Excel, VLOOKUP returns the value in a specified column that sits in the same row as the lookup value you searched for. For example, =VLOOKUP("A-100", A2:C10, 3, FALSE) finds “A-100” in column A and returns the matching value from column C. The function works by scanning the first column of a table for your lookup value, then pulling data from a column you choose to the right of it.

What VLOOKUP Actually Does

VLOOKUP stands for “vertical lookup.” It searches down the first column of a table (vertically) and returns a value from a cell in the same row, but from a column you specify. The syntax is:

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

Here is what each part means:

  • lookup_value – the value you want to find (e.g., a product ID, name, or number).
  • table_array – the range of cells that contains both the lookup column and the return column.
  • col_index_num – the column number in the table from which to return a value. Column 1 is the first column of the table, column 2 is the second, and so on.
  • range_lookup – TRUE for approximate match (needs sorted data) or FALSE for exact match. Most users should use FALSE.

For example, with product IDs in A2:A10 and prices in B2:B10, =VLOOKUP("P-203", A2:B10, 2, FALSE) returns the price for product P-203. If P-203 is in row 5, the formula returns the value from B5.

How to Build a VLOOKUP Formula Step by Step

  1. Click the cell where you want the result.
  2. Type =VLOOKUP(.
  3. Click the cell that contains the value you want to look up, or type it in quotes (e.g., "A-100").
  4. Select the table range that includes both the lookup column and the return column. Press F4 to make it absolute (e.g., $A$2:$C$10) so it doesn’t shift when you copy the formula.
  5. Type the column number from which to return the value (count from the left of your selected range).
  6. Type FALSE for an exact match, then close the parenthesis and press Enter.

Example: If you have a table with employee IDs in A2:A20 and names in B2:B20, and you type an ID in D2, the formula =VLOOKUP(D2, $A$2:$B$20, 2, FALSE) returns the employee’s name.

Exact Match vs. Approximate Match

The fourth argument, range_lookup, controls how VLOOKUP finds your value:

  • FALSE (exact match) – VLOOKUP looks for an exact match. If none is found, it returns #N/A. This is the safest and most common choice.
  • TRUE (approximate match) – VLOOKUP finds the largest value in the first column that is less than or equal to your lookup value. The first column must be sorted in ascending order, otherwise results are unpredictable. This is useful for finding tax brackets, discount tiers, or shipping rates.

For example, with a rate table sorted by weight (A2:A6) and shipping cost (B2:B6), =VLOOKUP(3.2, A2:B6, 2, TRUE) returns the cost for the largest weight less than or equal to 3.2. If the table has 1, 2, 5, 10, and 20, it returns the cost for 2.

Common Reasons VLOOKUP Returns #N/A

Seeing #N/A is the most frequent problem. It means VLOOKUP couldn’t find a match. Common causes:

  • No exact match exists – the value isn’t in the first column.
  • Leading or trailing spaces – e.g., “A-100 ” vs. “A-100”. Use =TRIM() on the lookup value or the data.
  • Numbers stored as text – the lookup value is a number but the table has text, or vice versa. Convert one to match the other.
  • Incorrect column index – you may be pointing to a column that doesn’t exist in the table range.
  • Table range doesn’t start at the lookup column – VLOOKUP always searches the first column of the table_array, so if you select B2:C10 but want to look up in A, it won’t work.

To fix, check the data types, trim spaces, and verify the table range starts with the column that contains your lookup values.

VLOOKUP vs. XLOOKUP: Which to Use?

XLOOKUP, available in Excel 2021 and Microsoft 365, is a modern replacement that fixes many VLOOKUP limitations:

  • XLOOKUP can return a value from any column, even to the left of the lookup column.
  • It defaults to an exact match, so you don’t need to specify FALSE.
  • It has built-in handling for missing values (you can set a custom message).
  • It can return multiple values with a single formula.

Example: =XLOOKUP("A-100", A2:A10, C2:C10) returns the value from C for the row where A equals “A-100”. If you have Microsoft 365 or Excel 2021, XLOOKUP is often easier and more robust. However, VLOOKUP still works in all versions and is widely used in older workbooks.

How to Return a Value from Another Sheet or Workbook

You can use VLOOKUP to pull data from a different sheet by including the sheet name in the table range:

=VLOOKUP(A2, 'Sheet2'!$A$2:$B$100, 2, FALSE)

For another workbook, include the workbook name in brackets: =VLOOKUP(A2, '[Sales.xlsx]Sheet1'!$A$2:$B$100, 2, FALSE). Make sure the other workbook is open, or use a full path if you want it to work when closed (though closed workbooks can be tricky).

Tips for Using VLOOKUP Effectively

  • Always use FALSE for exact matches unless you have a sorted table and need approximate matching.
  • Use absolute references (e.g., $A$2:$B$10) so you can copy the formula down without shifting the table range.
  • Wrap VLOOKUP in =IFERROR(VLOOKUP(...), "Not found") to display a friendly message instead of #N/A.
  • Keep your lookup column unique – duplicates cause VLOOKUP to return the first match only.
  • For large datasets, consider using INDEX/MATCH or XLOOKUP for better performance and flexibility.

Frequently Asked Questions

Why does VLOOKUP return the wrong value?

The most common cause is using TRUE (approximate match) on an unsorted table. If the first column isn’t sorted ascending, VLOOKUP can return an incorrect but plausible value. Switch to FALSE for exact match, or sort the first column if you need approximate matching.

Can VLOOKUP return a value from a column to the left?

No, VLOOKUP can only return values from columns to the right of the lookup column. If you need to return a value from a column to the left, use INDEX/MATCH or XLOOKUP. For example, =INDEX(A2:A10, MATCH("X", C2:C10, 0)) returns a value from column A based on a match in column C.

How do I return multiple columns with VLOOKUP?

VLOOKUP returns only one value per formula. To return multiple columns, you can write separate VLOOKUP formulas for each column index, or use XLOOKUP with a range return. For instance, =XLOOKUP(A2, A2:A10, B2:D10) returns three values in adjacent cells (spill range) in Microsoft 365.

Does VLOOKUP work with wildcards?

Yes, when using FALSE, you can use wildcards in the lookup value: * (any sequence) and ? (single character). For example, =VLOOKUP("A*", A2:B10, 2, FALSE) returns the first value where column A starts with “A”. Note that this only works with text, not numbers.

Why does VLOOKUP return #REF!?

#REF! means the column index number is larger than the number of columns in the table range. For example, if your table is A2:B10 (2 columns) and you ask for column 3, you get #REF!. Check your col_index_num and the range size.

Can I use VLOOKUP to return a value based on a cell reference that changes?

Yes, you can use a cell reference as the lookup value. If you type an ID in D2 and use =VLOOKUP(D2, $A$2:$B$10, 2, FALSE), the formula updates when you change D2. This is the standard way to create a search box.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.