How to Build a VLOOKUP in Excel

Formulas & functions · Updated November 20, 2026

To build a VLOOKUP, enter a formula that names the value to find, the lookup table, the return-column number, and whether you want an exact match. With product IDs in Orders!A2 and product IDs, names, and prices in Products!A2:C5, enter =VLOOKUP(A2,Products!$A$2:$C$5,3,FALSE) in Orders!B2 to return the matching price. In this example, if A2 contains P-208, the result is 18.50. The steps below show how to set up and check the formula.

What You’ll Need

  • A workbook in Microsoft Excel for Microsoft 365 on Windows. The VLOOKUP formula works in Excel for Mac and Excel for the web as well; the formula arguments are the same.
  • A lookup value, such as a product code or employee ID, that appears in the first column of your lookup table.
  • A table with the information you want to find. Decide which column contains the lookup value and which column contains the result you want returned.
  • Consistent data: codes should be stored in the same way in both places, including spaces, leading zeros, and number-versus-text formatting.

Step-by-Step

  1. Arrange the lookup table. VLOOKUP searches only the leftmost column of the table range you give it, so put the values you will search for at the left. For example, on a worksheet named Products, enter Product ID in A1, Product Name in B1, and Price in C1. Put four records in rows 2 through 5: P-104, Notebook, 4.50; P-208, Desk lamp, 18.50; P-315, USB hub, 12.00; and P-422, Mouse, 9.75. Keep each record together on one row and avoid blank rows in the middle of the list.
  2. Choose the value to look up. On a second worksheet named Orders, type a product ID in A2, such as P-208. The ID in that cell is the lookup value. In a real workbook, this might instead be an invoice number, part code, name, or another unique identifier. VLOOKUP will search for it in the first column of the table range, not in every column.
  3. Select the cell for the answer. Click Orders!B2, next to the ID. This is where the returned information will appear. Decide what you want to retrieve before you create the formula: a name, price, department, or other field. The column number in the formula depends on this choice.
  4. Enter the exact-match formula. With IDs in Orders!A2 and the Products table in Products!A2:C5, enter =VLOOKUP(A2,Products!$A$2:$C$5,3,FALSE) in Orders!B2. The first argument, A2, points to the ID to find. The second argument is the table range. The third argument, 3, says to return the value from the third column of that selected range, which is Price. FALSE requests an exact match, which is usually the right choice for codes, IDs, names, and other discrete values. Press Enter. Since P-208 is the second record and its price is 18.50, B2 should display 18.50.
  5. Understand the table-column number. Count from the left edge of the range, not from the worksheet’s column letters. In Products!A2:C5, column A is number 1, B is number 2, and C is number 3. To return the product name instead, use this formula. With the same IDs in Orders!A2 and the same lookup table in Products!A2:C5, enter =VLOOKUP(A2,Products!$A$2:$C$5,2,FALSE) in Orders!B2; for P-208, it returns Desk lamp. If you change the table range to start in column B, the numbering changes too, and the lookup ID would no longer be in the first column.
  6. Lock the table range before filling down. The dollar signs in $A$2:$C$5 make the table reference absolute, so it stays fixed when the formula is copied to other rows. The lookup reference A2 is relative, so it changes to A3, A4, and so on as you copy. Without the dollar signs, the lookup range may shift and cause later formulas to search the wrong cells.
  7. Copy the formula for additional rows. Select B2 and drag the small square at the lower-right corner of the cell down the rows that need results. Alternatively, copy B2, select the destination cells below it, and paste. Check that each row uses the ID on the same row and that the table range remains $A$2:$C$5. You can also double-click the fill handle if the adjacent column has a continuous list of IDs and Excel can detect the appropriate fill length.
  8. Check the result against the source. Pick one or two IDs and compare the returned value with the corresponding row on Products. Confirm that the answer is from the intended field and that the lookup table range includes every record. If the result is unexpected, inspect the formula’s range and column number before changing the data.

VLOOKUP’s basic structure is lookup value, table range, return-column number, and match option. For an exact match, use FALSE as the final argument. If you omit that argument, Excel assumes an approximate match, which can produce a plausible but incorrect result when the first column is not sorted. In the formula examples, commas separate arguments. Some regional Excel settings use semicolons instead; if Excel rejects a formula with commas, check the list separator used by your installation.

Common Mistakes

  • Putting the lookup column anywhere but first. VLOOKUP cannot search to the left within its selected table range. If the key is in column C and the value you want is in column A, rearrange the source so the key is first, choose a different lookup method, or use XLOOKUP if your Excel version supports it.
  • Using the wrong return-column number. The number counts columns inside the selected range, not worksheet columns. A range that begins in column B treats B as column 1. A wrong number may return a value without showing an error, so verify the intended field.
  • Leaving out FALSE for an exact lookup. Approximate matching is not suitable for most IDs and codes. It can return a neighboring value, especially if the lookup column is unsorted. Specify FALSE when you need the value to match exactly.
  • Using a range that moves when copied. If the lookup table shifts as you fill formulas down, later rows can return errors or incorrect results. Lock both ends of the table range with dollar signs.
  • Comparing values that look alike but are different. A number stored as text may not match a numeric value; a hidden leading or trailing space can also prevent a match. Leading zeros matter too: 0017 and 17 are not necessarily the same ID.

Tips

  • Use a unique key, such as a product ID, rather than a description that may appear on several rows. VLOOKUP returns the first matching record it encounters, so duplicate keys can give an unexpected answer.
  • Keep the lookup table and formula easy to audit. Use clear headings, avoid merged cells in the source data, and keep the lookup key and returned fields in neighboring columns where practical.
  • Format the result cell to suit its contents. For prices, use a currency or number format; formatting changes how a value looks, not what VLOOKUP retrieves.
  • If the source list grows often, consider converting it into an Excel table using Insert > Table. A structured table reference can expand as records are added, though the VLOOKUP column number still depends on the selected range’s column order.
  • For a one-off lookup, test the formula on a known ID first. A known example makes it easier to catch a shifted range or wrong return column before filling a long list.
  • VLOOKUP can return a value from a column to the right of the lookup column, but not from one to its left. In Microsoft 365, XLOOKUP offers more flexible lookup and return ranges; VLOOKUP remains useful for compatibility with older workbooks.

Troubleshooting

If you see #N/A: Excel did not find an exact match, or the lookup value is not in the first column of the selected range. Confirm the spelling and punctuation, check for extra spaces, and compare the underlying value types. If the codes should be text, format both source and lookup values consistently and re-enter or convert the values as needed. Also confirm that the table range includes the row you expect.

If you see #REF!: The return-column number is larger than the number of columns in the table range, or part of the formula refers to a deleted cell or range. Count the columns in the selected range and adjust the number so it points to an existing field. For a three-column range, valid return-column numbers are 1, 2, and 3.

If you see #VALUE! or a formula error: Check that the column number is a positive number and that the parentheses and argument separators are correct. In some regional settings, Excel requires semicolons between arguments rather than commas. If the cell displays the formula as text, change its number format to General and enter the formula again; also check that the formula does not begin with an apostrophe.

If the formula returns the wrong value: Verify the table range starts with the lookup column, the return-column number targets the intended field, and the final argument is FALSE. Look for duplicate keys, because VLOOKUP returns the first match. When using approximate matching intentionally, the first column must be sorted in ascending order; otherwise, results may be unreliable.

If you want a friendly message instead of #N/A when an ID is absent, you can wrap the lookup in IFERROR. With the same Products table in Products!A2:C5 and a missing ID P-999 in Orders!A2, enter =IFERROR(VLOOKUP(A2,Products!$A$2:$C$5,3,FALSE),"Not found") in Orders!B2; it returns Not found. Use this only when a missing match is an expected possibility, since IFERROR can also hide other problems in the formula.

Frequently Asked Questions

What does VLOOKUP do in Excel?

VLOOKUP searches down the first column of a selected range for a value and returns information from another column in the same row. For example, it can find a product ID and return its price. It searches vertically, which is what the V in its name refers to.

Should I use TRUE or FALSE in VLOOKUP?

Use FALSE for an exact match, which is the appropriate setting for most identifiers and text lookups. TRUE requests an approximate match and generally requires the first column to be sorted in ascending order. If you are unsure, use FALSE.

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

No. The lookup value must be in the leftmost column of the selected range, and VLOOKUP returns from a column to its right or from that same column. Rearrange the data or use XLOOKUP in Microsoft 365 or Excel 2021 if you need a lookup that can return values from either direction.

Why does VLOOKUP return the wrong row?

Common causes include using TRUE or omitting the match argument, duplicate lookup values, a range that starts in the wrong column, or a return-column number that points to a different field. Use FALSE for exact matching and inspect the first matching source row.

Does VLOOKUP update when the source data changes?

Yes. The returned result recalculates when the referenced source value changes, provided Excel calculation is enabled and the formula still points to the correct range. If you append new rows outside the range, expand the range or use a table-based reference that includes them.

Is VLOOKUP available in Excel for Mac and the web?

Yes. VLOOKUP is available in Excel for Windows, Mac, and the web. The formula syntax is the same, although keyboard shortcuts and some interface details differ by platform. The formulas in this guide use standard comma separators; local regional settings may require semicolons instead.

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.