Excel Vertical Lookup: VLOOKUP, XLOOKUP, and Tips

Formulas & functions · Updated November 18, 2026

An Excel vertical lookup searches down the first column of a table for a value, then returns related information from another column on the same row. Use VLOOKUP for broad compatibility, or XLOOKUP in Microsoft 365 and Excel 2021 or later for a more flexible option.

What a vertical lookup does

A vertical lookup is useful when your data is arranged in columns and each row describes one record. For example, a product list might have product codes in the first column, names in the second, and prices in the third. A lookup takes a code, finds its row, and returns the corresponding name or price.

The word “vertical” describes the search direction: Excel examines values down a column. It does not mean that the result must be displayed vertically. The formula can return one value to a cell, and you can copy it down to look up many records.

Lookups are common in inventory lists, staff directories, price tables, and reports that join information from separate sheets. They save you from manually scanning a large table, but they depend on consistent data: a code with an extra space or a different data type may not match the value you expect.

How VLOOKUP works

VLOOKUP searches the leftmost column of a selected table range. Once it finds a match, it returns a value from the same row, using a column number counted from the left edge of that range. The function cannot search a later column and return a value from a column to its left.

For a concrete example, suppose A1:C6 has the headings Product code, Product, and Price. Rows 2 through 6 contain product data, and D2 contains the code P-104. The table range begins at column A, so its third column is the price column. With the product table in A2:C6 and the search code in D2, enter this formula in E2:

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

If the row for P-104 contains a price of 18.50, the formula returns 18.50. The final argument, FALSE, requests an exact match. This is usually the right choice for identifiers such as product codes, employee IDs, and account numbers.

VLOOKUP’s arguments are, in order, the value to search for, the table range, the number of the return column within that range, and the match setting. The column number is not an absolute worksheet column number: in A2:C6, A is 1, B is 2, and C is 3. If you change the range to B2:C6, the price column becomes column 2 within that range.

Using XLOOKUP instead

In Microsoft 365 and Excel 2021 or later, XLOOKUP is often easier to maintain. It uses separate search and return ranges, so you do not have to count a return-column index. It can also search in either direction and lets you specify what to display when no match is found.

With product codes in A2:A6, prices in C2:C6, and the lookup code P-104 in D2, enter this formula in E2:

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

If P-104 has a price of 18.50, the result is 18.50. If D2 contains a code absent from A2:A6, the result is Not found instead of the usual not-found error. The separate ranges should line up row for row: A2:A6 and C2:C6 each contain five cells and represent the same records.

To return a product code by searching for a price, XLOOKUP can search the price column and return from the code column. With prices in C2:C6, product codes in A2:A6, and 18.50 in G2, enter this formula in H2:

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

If 18.50 belongs to P-104, the result is P-104. When more than one row has that price, XLOOKUP returns the first matching row by default. A price is not necessarily a unique identifier, so this reverse lookup may be ambiguous in a real product table.

Choosing the right match type

For most record lookups, choose exact matching. In VLOOKUP, specify FALSE or 0 as the fourth argument. If that argument is omitted, VLOOKUP uses approximate matching, which can return an unexpected result when the first column is not sorted as required.

XLOOKUP uses exact matching by default. Its optional match-mode argument can change the behavior, including options for an exact match or the next smaller or larger item. Approximate matching is useful for tasks such as assigning a tax or grading band from a sorted threshold table, but it should be set deliberately and checked against the table’s sort order.

  • Use exact matching for distinct text or numeric keys, such as SKU codes, names, or invoice numbers.
  • Use approximate matching only when the table is designed for ranges or thresholds and the order meets the function’s requirements.
  • Check whether duplicate lookup values exist. Both functions normally return the first matching row in their search direction.

Entering and copying a lookup formula

On Windows, select the cell where the answer should appear, type the formula, and press Enter. On a Mac, the same formula entry process applies; Excel formulas use commas as argument separators in the examples here, though regional settings can affect separators. In Excel for the web, enter the formula in a cell as you would in the desktop app.

To look up several codes, put one code in each row and copy the formula down. For instance, if the search value is D2, copying a formula that refers to D2 normally changes that reference to D3, D4, and so on. If you need a range to stay fixed while copying, use absolute references with dollar signs. With product codes in A2:A6, prices in C2:C6, and the search code in D2, this VLOOKUP formula locks the table range but allows the lookup cell to change when copied down:

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

The formula still returns the matching price, such as 18.50 for P-104. Absolute references are particularly helpful when filling formulas across many rows; without them, Excel may shift the lookup table and produce incorrect results.

Common problems and how to fix them

A lookup that returns #N/A usually means Excel did not find a matching value, though the cause may be less obvious than a genuinely missing record. Compare the searched value and lookup column carefully. Text numbers and numeric values can behave differently, and copied data may contain leading, trailing, or nonbreaking spaces.

  • Check the lookup column: VLOOKUP searches only the first column in its table range. Move or restructure the range, or use XLOOKUP to select separate search and return ranges.
  • Check the match setting: Set VLOOKUP’s last argument to FALSE for exact matching. An omitted argument can cause approximate-match behavior.
  • Check the return-column index: Count from the first column of the selected VLOOKUP range, not from worksheet column A.
  • Check for hidden differences: Look for extra spaces, punctuation differences, inconsistent spelling, or a number stored as text.
  • Check the reference range: When copying formulas, confirm that the table range has not shifted. Use absolute references when it should remain fixed.

If the formula returns the wrong record rather than an error, check for duplicate keys. An exact match does not guarantee that a key is unique. Also verify that the return column belongs to the same row as the matching key; with XLOOKUP, the search and return ranges must correspond to the same records.

VLOOKUP, XLOOKUP, and other alternatives

VLOOKUP remains useful when a workbook must work in older Excel versions or when an existing workbook already uses it. Its main structural limitation is that the search column has to be the first column of its table range, and its numbered return column can become fragile if columns are inserted or rearranged.

XLOOKUP removes those particular constraints, supports searching in either direction, and offers a built-in not-found result. It is not available in every older Excel release: Microsoft 365 and Excel 2021 or later support it, while people opening a workbook in an older version may see a function-name error. Consider the versions used by everyone who needs the file before replacing older formulas.

INDEX and MATCH can also create flexible lookups in older Excel versions. They are useful when XLOOKUP is unavailable and the return column is to the left of the search column, but the combined formula is less straightforward for many beginners. For a simple vertical lookup, VLOOKUP or XLOOKUP is generally easier to read.

Frequently Asked Questions

What is the vertical lookup formula in Excel?
Use VLOOKUP to search down the first column of a table and return a value from another column in the matching row. For exact matching, include FALSE as its final argument. XLOOKUP is a more flexible choice in supported Excel versions.

Why does VLOOKUP return #N/A when the value is in the table?
The value may not be an exact match because of spaces, inconsistent text, or a number stored as text. Also confirm that the value is in the first column of the VLOOKUP range and that the formula is using an exact-match setting.

Can VLOOKUP return a value from a column to the left?
No. VLOOKUP searches the first column in its table range and returns from a column to its right. Use XLOOKUP, or an INDEX-and-MATCH combination in versions without XLOOKUP, when you need to return a value from the left.

Is XLOOKUP better than VLOOKUP?
For many newer workbooks, XLOOKUP is easier to adapt because it uses separate search and return ranges, can look in either direction, and can specify a not-found result. VLOOKUP may be preferable for compatibility with older Excel versions or established workbooks.

Does VLOOKUP find every matching row?
No. VLOOKUP returns one result: the first matching row it encounters. If a key appears more than once, use a method designed to return multiple matches, such as FILTER in Microsoft 365 or Excel 2021 and later.

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.