VLOOKUP in Excel: A Step-by-Step Example

Formulas & functions · Updated November 20, 2026

To use VLOOKUP in Excel, put the value you want to find in a cell, select a table whose leftmost column contains that value, and enter a formula such as =VLOOKUP(E2,A2:C6,3,FALSE). If E2 contains P-104 and the matching row in A2:C6 has a price of 14.99 in its third column, the formula returns 14.99.

Set Up the Example Data

VLOOKUP is useful when you have a list of records and want Excel to retrieve one detail from the row matching a value. For this example, enter the following small product list in cells A1:C6:

  • A1: SKU; B1: Product; C1: Unit price
  • A2: P-101; B2: Notebook; C2: 3.50
  • A3: P-102; B3: Pen; C3: 1.25
  • A4: P-103; B4: Folder; C4: 2.00
  • A5: P-104; B5: Desk lamp; C5: 14.99
  • A6: P-105; B6: Stapler; C6: 6.49

In E2, enter P-104 as the SKU to look up. The lookup value can come from a typed entry, another formula, or a cell elsewhere in the workbook. Keeping it in its own cell makes it easy to reuse the formula with a different SKU later.

Enter the VLOOKUP Formula Step by Step

  1. Click the cell where you want the answer to appear, such as F2.
  2. Type the formula below. Here, the lookup value is in E2, the product list is in A2:C6, and the price is in the third column of that selected range.
  3. Press Enter. Excel returns 14.99 in F2 for P-104.

With the SKU in E2 and the lookup table in A2:C6, use this formula in F2 to return the unit price:

=VLOOKUP(E2,A2:C6,3,FALSE)

To return the product name instead, use the same lookup value and range but request the second column. With the product name in column B, the result for P-104 is Desk lamp:

With the SKU in E2 and the lookup table in A2:C6, use this formula to return the product name:

=VLOOKUP(E2,A2:C6,2,FALSE)

In Microsoft 365 for Windows, enter the formula in the formula bar or directly in the selected cell. The same formula syntax works in Excel for Mac; keyboard shortcuts differ, but the formula arguments do not. In Excel for the web, formulas are entered in the cell or formula bar in the same way.

What Each VLOOKUP Argument Means

VLOOKUP has four arguments, written in this order: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The square brackets in function descriptions indicate an optional argument; do not include those brackets in a formula. In the example, each argument has a specific job:

  • lookup_value: E2 contains P-104, the value Excel searches for.
  • table_array: A2:C6 is the table range. Its leftmost column, A, must contain the SKUs being searched.
  • col_index_num: 3 tells Excel to return a value from the third column of the selected range. It counts from the left edge of A2:C6, not from the worksheet’s column letters.
  • range_lookup: FALSE requests an exact match. TRUE requests an approximate match. Leaving this argument out makes VLOOKUP use approximate matching, so specifying FALSE is safer for most everyday lookups.

VLOOKUP searches only the first column of table_array and returns a value from a column to its right. It cannot use this function to search column B and return a value from column A. The returned information also comes from the same row as the match, which is why each SKU should identify the correct product record.

Choose Exact or Approximate Matching

For product codes, names, employee IDs, invoice numbers, and most other identifiers, use FALSE for an exact match. If Excel cannot find the requested value, it usually returns #N/A rather than choosing a different record. Exact matching does not require the first column to be sorted.

Approximate matching, selected with TRUE, is designed for ordered thresholds, such as a rate band or a grading scale. In that mode, Excel expects the first column to be sorted in ascending order and returns the closest value that is less than or equal to the lookup value. If the data is not sorted correctly, the result may be unexpected. For example, a threshold table can be appropriate for approximate matching; a list of unordered SKUs is not.

A frequent misconception is that TRUE simply means “allow a close spelling” or “find anything similar.” It does not perform fuzzy text matching. It changes how Excel handles ordered lookup values. For ordinary text lookups, FALSE is generally the appropriate choice.

Copy the Formula Down a List

If you need a result for several SKUs, place one lookup value in each row and fill the formula down. The lookup reference should move from E2 to E3, E4, and so on, while the product table should stay fixed. Add dollar signs to the table range to make it an absolute reference.

With lookup values in E2:E10 and the product table still in A2:C6, enter this formula in F2 before filling it down; for E2 containing P-104, the result is 14.99:

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

Select F2 and drag its fill handle down the column, or copy F2 and paste into the cells below. On Windows, Ctrl+C and Ctrl+V copy and paste; on a Mac, use Command+C and Command+V. The dollar signs keep the lookup range anchored as the formula is copied, while E2 remains relative so that it changes to E3, E4, and so forth. Without the dollar signs, a copied range may shift and eventually exclude some of the source data.

If you add more products below row 6, revise the range so the new rows are included, or use an Excel Table for data that will grow. A fixed range such as A2:C6 does not automatically expand to include records entered below it. You can select the data and choose Insert > Table to create a table, then manage its name on the Table Design tab. If you choose a named table as the lookup range, make sure the formula refers to the correct table and that its first column is still the lookup field.

Common Errors and How to Fix Them

When a VLOOKUP result looks wrong, check the lookup value, range, column number, and match setting before rebuilding the formula. These are common causes:

  • #N/A: Excel did not find an exact match. Check for spelling differences, extra spaces, or a lookup value stored as text when the corresponding table value is numeric (or the reverse). Confirm that the lookup column is included as the first column of the selected range.
  • #REF!: The column index is larger than the number of columns in table_array. For A2:C6 there are three columns, so an index of 4 is outside the selected range.
  • Wrong result: Check that the last argument is FALSE when you need an exact match. Also verify that the column index counts from the left edge of the selected range, not from column A of the worksheet in every possible formula.
  • Formula changes unexpectedly when copied: Anchor the table range with dollar signs, as in $A$2:$C$6, while leaving the lookup cell relative if each row has a different lookup value.
  • A match appears to be missing: Look for leading or trailing spaces, inconsistent punctuation, and text-versus-number differences. VLOOKUP does not distinguish between uppercase and lowercase text, but otherwise the compared values need to match for an exact lookup.

When debugging, test one known value directly in the lookup column and confirm the matching row is inside the selected range. Also count the columns in that range from left to right. This simple check often catches a mistaken range or column index faster than changing several parts of the formula at once.

VLOOKUP Compared with XLOOKUP

VLOOKUP remains useful in workbooks that need to support older Excel versions or that already rely on established lookup formulas. Its main structural limitation is that the lookup column must be the leftmost column of the selected range, and the return-column number is a numeric index. Inserting or rearranging columns can make that index less intuitive or cause it to point at a different field.

In Excel for Microsoft 365 or Excel 2021, XLOOKUP can search one range and return a value from a separate range. With the SKU in E2, SKUs in A2:A6, and prices in C2:C6, this formula returns 14.99 for P-104 and displays Not found if there is no match:

=XLOOKUP(E2,A2:A6,C2:C6,"Not found")

XLOOKUP does not require the return range to be to the right of the lookup range, and it uses exact matching by default. It is a useful option in newer Excel versions, but VLOOKUP is still worth understanding because it appears in many existing spreadsheets and is compatible with a wider range of Excel releases. If a workbook will be shared with people using older versions, check which functions their Excel supports before replacing a formula.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visible?
Often, the values only look identical. One may contain an extra space, or one may be stored as text while the other is numeric. Check the source cells and make sure the lookup value is in the first column of the selected range.

Does VLOOKUP search from right to left?
No. VLOOKUP searches the leftmost column in its table range and returns a value from a column to its right. Use XLOOKUP in a supported version of Excel when you need to search one range and return a value from a range on its left.

What does the number 3 mean in a VLOOKUP formula?
It means return the value from the third column of table_array. If the range is A2:C6, the third column is C. The number is relative to the selected range, not a request for worksheet column C in every formula.

Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match, which is the right choice for most identifiers and ordinary text lookups. Use TRUE only when approximate matching is intentional and the first column is sorted in ascending order.

Can VLOOKUP return more than one column?
A standard VLOOKUP formula returns a value from one column for each lookup. You can place separate VLOOKUP formulas in neighboring cells with different column-index numbers, or use a newer function such as XLOOKUP when it fits your version and layout.

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.