Basic VLOOKUP in Excel: Formula, Examples, and Fixes

Formulas & functions · Updated November 21, 2026

In Excel, VLOOKUP searches for a value in the leftmost column of a range and returns a value from another column on the same row. For a basic exact-match lookup, enter =VLOOKUP(E2,$A$2:$C$6,3,FALSE) in the result cell, assuming the value to find is in E2 and the lookup table is in A2:C6; Excel returns the matching row’s value from the third column.

What VLOOKUP is used for

VLOOKUP is short for vertical lookup. It is useful when a list is arranged in rows and you want to retrieve information associated with a value in the first column. For example, a product list might have product IDs in column A, product names in column B, and prices in column C. Enter an ID elsewhere, and VLOOKUP can return the matching name or price without manually scanning the list.

The function is common in inventory lists, staff directories, price sheets, and reports that combine details from two tables. It does not merge tables or change the source data; it returns a value in the cell containing the formula. If the lookup value is not present, an exact-match lookup normally returns an error rather than an approximate result.

VLOOKUP is a longstanding Excel function and remains available in Microsoft 365. Its basic behavior is the same on Windows, Mac, and Excel for the web, although ribbon layouts and some interface details can differ. The examples below use standard Excel formulas and comma argument separators.

Set up the lookup table

Before writing the function, check that the data is organized so the value you want to search for is in the first column of the selected range. Imagine a table with these rows:

  • A2:C2: P-101, Mug, 12.50
  • A3:C3: P-102, Notebook, 4.75
  • A4:C4: P-103, Pen set, 8.00
  • A5:C5: P-104, Desk lamp, 24.99
  • A6:C6: P-105, Folder, 3.25

Suppose E2 contains P-104 and you want the price to appear in F2. The lookup range begins in column A because that is where the product IDs are. The returned price is in column C. The first row shown is row 2, so the lookup range is A2:C6; it does not include the headings in row 1.

Headings are useful for people reading the sheet, but VLOOKUP searches values in the first column of its range. Including a text heading usually does not cause a problem with an exact search, but selecting only the data rows makes the intended range clearer and avoids accidentally treating a heading as a possible result.

Enter the basic formula

With P-104 in E2, product IDs in A2:A6, product names in B2:B6, and prices in C2:C6, enter this formula in F2:

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

The formula returns 24.99, the price in column C on the row where column A contains P-104. To use it, select the result cell, type the formula, and press Enter. In Excel for Windows, Enter confirms the entry; on Mac, Enter or Return confirms it depending on keyboard settings. You can also use the fill handle to copy a formula into neighboring cells.

The dollar signs make the table range absolute. If you copy the formula down, E2 changes to E3, E4, and so on, while $A$2:$C$6 stays fixed. Without the dollar signs, both the lookup reference and the range may shift as you copy, which can make later results incorrect. If you are only entering one formula and will not copy it, a relative range can still work.

Understand the four arguments

VLOOKUP has four arguments: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The square brackets in that syntax indicate an optional argument; do not type the brackets into a formula. Each argument has a specific job:

  • lookup_value: The value Excel searches for, such as E2. It can be a cell reference or a value typed directly into the formula.
  • table_array: The range containing both the search column and the column with the answer, such as $A$2:$C$6.
  • col_index_num: The number of the return column counted from the left edge of the selected range. In A2:C6, column A is 1, B is 2, and C is 3.
  • range_lookup: TRUE or FALSE to choose approximate or exact matching. FALSE requests an exact match.

The column number is relative to the selected range, not the worksheet’s column letters. If the range were B2:D6, column B would be number 1 and column D would be number 3. A frequent mistake is to enter the worksheet column number rather than the position within the lookup range.

Choose exact or approximate matching

For IDs, names, order numbers, and most ordinary lookups, use FALSE for an exact match. Excel looks for a value that matches the lookup value and returns a result from that row. Text matching is generally not case-sensitive, so names that differ only by capitalization are not treated as distinct matches.

TRUE requests an approximate match. In that mode, the first column of the lookup range should be sorted in ascending order. Excel returns an exact match if it finds one; otherwise, it returns the result for the largest first-column value that is less than the lookup value. If the data is unsorted, the result may be unexpected. Approximate matching is useful for ranges such as grading bands or commission thresholds, but it is not a safe default for unique IDs.

If you leave off the fourth argument, Excel uses approximate-match behavior. That omission is a common source of confusing results: the formula can return a value even when there is no exact match. For clear, predictable basic lookups, include FALSE explicitly.

Copy the formula down a list

If E2:E10 contains IDs and you want each corresponding price in F2:F10, enter the formula in F2 using the fixed lookup range, then copy it down. The lookup cell reference should adjust for each row, while the table reference should remain fixed. In Excel for Windows, Ctrl+D fills selected cells downward when the top cell contains the formula; on Mac, Command+D is the usual Fill Down shortcut. The fill handle is another option on both platforms.

After copying, check a few rows by comparing the returned values with the source table. This is especially important if the source range contains gaps, repeated IDs, or has been sorted or edited. If the list grows later, a fixed range ending at row 6 will not automatically include new data below row 6. You will need to expand the range or use an Excel Table so the lookup source can grow with the data.

Common errors and how to fix them

#N/A usually means an exact match was not found. Check for leading or trailing spaces, misspellings, and values stored in different formats. For example, the number 104 and text “104” can look alike but may not compare as the same value. Also confirm that the lookup value is actually in the first column of the selected range.

#REF! can appear when the column index is greater than the number of columns in the selected range, or when a referenced column has been deleted. Count the return column from the left edge of the table range and verify that the range still contains it.

#VALUE! can result if the column index is not a usable number. Check that the third argument is a positive integer that identifies a column in the table range. If the result looks wrong but no error appears, verify whether the fourth argument is TRUE or omitted, and check that the first matching key is the one you intended.

Duplicate lookup values are another subtle issue. VLOOKUP returns the first matching row it encounters, moving from the top of the first column downward. It does not report every match or automatically choose the newest record. If duplicates are possible, decide which row should count or make the lookup key unique.

Know VLOOKUP’s limitations

VLOOKUP can return data only from a column to the right of its search column. The lookup column must be the leftmost column in the table range, and the column index is a number that can become outdated if someone inserts or rearranges columns. These constraints do not stop basic lookups from working, but they matter when a workbook changes over time.

It also returns a single value rather than a list of all matching rows. If the task is to find several records for one name or ID, a single VLOOKUP is not enough. And because duplicate keys return the first match, a result can look plausible while still being the wrong record. Keeping keys unique and documenting the table layout helps prevent these problems.

VLOOKUP compared with newer alternatives

In Microsoft 365, XLOOKUP is often more flexible for new workbooks: it can search in one range and return a value from a separate range, including a column to the left. It also uses an exact match by default. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not available in some older Excel releases. VLOOKUP remains useful when a workbook must support older versions or when colleagues already use its familiar structure.

INDEX and MATCH can also perform lookups without VLOOKUP’s left-to-right restriction, though their formulas have a different structure. For a simple table where the key is on the left and the answer is to its right, VLOOKUP is often easier to read. Choose based on the workbook’s compatibility requirements, the direction of the lookup, and whether the formula needs to return one result or multiple results.

Frequently Asked Questions

What is the simplest VLOOKUP formula?
For an exact match, use the pattern =VLOOKUP(lookup_value,table_range,column_number,FALSE). Replace the placeholders with a value to find, a range whose first column contains that value, and the position of the result column within that range.

Why does VLOOKUP return #N/A when the value is visible?
The displayed values may differ in a way that is hard to see, such as an extra space or a number stored as text. Confirm that the lookup column is the first column of the range, that the values have compatible types, and that the formula uses the correct lookup cell.

Does VLOOKUP need the table sorted?
Not when the fourth argument is FALSE. Exact-match VLOOKUP works without sorting the table. If you use TRUE or omit the fourth argument for approximate matching, the first column should be sorted in ascending order.

Can VLOOKUP look to the left?
No. The search column must be the leftmost column in the selected range, and the return column must be to its right. In Microsoft 365 or Excel 2021 and later, XLOOKUP can look in either direction.

Why does VLOOKUP return the wrong duplicate?
It returns the first matching value from the top of the lookup column. If the key appears more than once, sort order or later duplicates do not make VLOOKUP select a different match. Use a unique key or another method designed to retrieve multiple or specifically selected matches.

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.