Excel VLOOKUP, HLOOKUP, and XLOOKUP: Key Differences

Formulas & functions · Updated November 18, 2026

VLOOKUP searches down the first column of a table, HLOOKUP searches across its first row, and XLOOKUP searches a chosen range and returns a result from a corresponding range. For most new workbooks, XLOOKUP is the most flexible choice; VLOOKUP and HLOOKUP remain useful when you need to understand or maintain older spreadsheets.

What the three lookup functions do

Lookup functions find a value in one part of a worksheet and return a related value from another part. For example, a product list can use a product code to retrieve its price. The key difference is the direction and structure each function expects.

  • VLOOKUP means vertical lookup. It looks for a value in the first, leftmost column of a selected table, then returns a value from another column on the same row.
  • HLOOKUP means horizontal lookup. It looks in the first, top row of a selected table, then returns a value from another row in the same column.
  • XLOOKUP looks in a specified lookup range and returns a result from a separate return range. The ranges can be arranged vertically or horizontally, and the return range can be to either side of the lookup range.

These functions do not search for a word anywhere in a worksheet. You tell Excel which range contains the lookup values and which range contains the results. If the lookup value is missing, the function may return an error unless you use an available not-found option or handle the error another way.

How VLOOKUP and HLOOKUP work

VLOOKUP takes four main arguments: the value to find, the table to search, the number of the result column within that table, and whether to use an approximate or exact match. The column number is counted from the left edge of the selected table, not from the worksheet. For example, column 2 means the second column in the selected range.

HLOOKUP uses the same general logic, but its result-row number is counted down from the top row of the selected table. Both functions can return results from only one direction: VLOOKUP returns values in columns to the right of the lookup column, while HLOOKUP returns values in rows below the lookup row.

The fourth argument matters. Set it to FALSE for an exact match, which is usually what you want for codes, names, and IDs. If it is omitted or set to TRUE, Excel uses an approximate match. Approximate matching expects the first column or row to be sorted in ascending order; otherwise, the answer can be unexpected. The fact that a formula returns a value does not prove that it found the intended record.

How XLOOKUP differs

XLOOKUP separates the lookup range from the return range. It does not require you to select one rectangular table or count a result column or row. It also uses exact match by default, can return a result from either side of the lookup range, and includes an optional argument for a custom message when there is no match.

Its core argument order is lookup value, lookup array, return array, optional not-found result, optional match mode, and optional search mode. Match mode can request an exact match, an exact match or next smaller or larger item, or a wildcard match. Search mode can search from the first item or last item; binary search options are available for appropriately sorted data. For ordinary unsorted lists, the default search is generally the straightforward choice.

XLOOKUP can return a value from a single row or column and can also return multiple adjacent results when the formula is entered in a compatible version of Excel. This is one example of dynamic-array behavior: a result may spill into neighboring cells. Make sure those cells are empty, or Excel may show a spill error.

Worked examples with the same product data

Suppose a worksheet has product codes in A2:A5 and prices in B2:B5. The rows contain P-101 at 12.50, P-102 at 9.00, P-103 at 18.75, and P-104 at 7.25. Cell D2 contains P-103. With that layout, this VLOOKUP searches the first column of A2:B5, returns the second column, and requires an exact match: =VLOOKUP(D2,A2:B5,2,FALSE). The result is 18.75.

With the same product codes in A2:A5, prices in B2:B5, and P-103 in D2, this XLOOKUP searches the code range and returns the corresponding price. The not-found argument supplies a readable result if the code is absent: =XLOOKUP(D2,A2:A5,B2:B5,"Not found"). For the stated data, the result is 18.75.

For a horizontal example, suppose B1:E1 contains P-101, P-102, P-103, and P-104, while B2:E2 contains their respective prices: 12.50, 9.00, 18.75, and 7.25. If G2 contains P-103, this formula searches the first row of B1:E2 and returns the second row: =HLOOKUP(G2,B1:E2,2,FALSE). Its result is 18.75. The same layout can be handled with XLOOKUP by using B1:E1 as the lookup array and B2:E2 as the return array.

Which function should you choose?

For a new formula in a current Excel workbook, XLOOKUP is often the clearest option. It avoids fragile position numbers, supports exact matching by default, and can look in either direction. It is particularly useful when a lookup column is not the leftmost column of a table or when the result column may move as the workbook changes.

VLOOKUP is a reasonable choice when a workbook already uses it, when you need to support older Excel versions, or when the lookup data is a conventional table with the key in its leftmost column. HLOOKUP is less common in many business lists because records are usually arranged in rows, but it is suitable for data laid out across columns, such as periods or categories in a header row.

  • Choose XLOOKUP for flexible new formulas and clearer lookup and return ranges.
  • Choose VLOOKUP when working with older files or when compatibility with older Excel releases is essential.
  • Choose HLOOKUP when the lookup values genuinely run across the top row of a range.
  • Consider reorganizing data into one record per row if a horizontal layout makes sorting, filtering, or analysis awkward.

Exact matches, approximate matches, and sorted data

A common misconception is that VLOOKUP always finds an exact match. It does not: if its fourth argument is left out, the function uses approximate matching. For a product code or employee ID, an approximate result can be misleading because a nearby value is not a valid substitute. Specify FALSE for exact VLOOKUP or HLOOKUP matches.

Approximate matching is useful for ordered bands, such as assigning a label to a score using threshold values. In this kind of task, the lookup values should be arranged in ascending order and the thresholds should be designed carefully. XLOOKUP offers explicit match modes for exact matches and nearest smaller or larger matches, so the intent can be stated in the formula rather than relying on an omitted VLOOKUP argument.

Wildcards are another distinct matching choice. In VLOOKUP or HLOOKUP, wildcard characters such as an asterisk can be used with exact-match mode for text patterns. XLOOKUP has a wildcard match mode. A wildcard match is not the same as an ordinary exact match: an asterisk can stand for a sequence of characters, and a question mark can stand for one character. If the lookup text itself contains wildcard characters, check Excel’s wildcard rules before assuming they will be treated literally.

Common errors and ways to prevent them

#N/A usually means the function did not find a match, although formatting differences can make two values that look alike behave differently. A number stored as text may not match a numeric value; leading or trailing spaces can also affect text lookups. Check the source values and data types before changing the formula.

#REF! can occur when a VLOOKUP or HLOOKUP result index points outside the selected table. A VLOOKUP column index of 3 cannot return a value from a two-column range. A related maintenance risk is inserting or rearranging columns: the formula’s fixed index can still point to a valid column but now return the wrong field. XLOOKUP’s separate return range makes that particular positional problem less likely.

Other useful checks include:

  • Confirm that the lookup range includes the values you intend to search and that the return range lines up with it.
  • For VLOOKUP, ensure the lookup values are in the table’s first column; for HLOOKUP, ensure they are in its first row.
  • Use exact matching for unique identifiers, and inspect duplicate keys because a lookup normally returns one matching result rather than resolving the ambiguity for you.
  • When copying formulas, consider whether the ranges should stay fixed. Absolute references such as $A$2:$B$5 can prevent a range from shifting when a formula is filled down.
  • For an XLOOKUP result that spills, check whether cells in the intended output area are occupied.

Excel version and compatibility

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including current Excel versions for Windows, Mac, and the web. It is not available in Excel 2019 or Excel 2016. Opening a workbook containing XLOOKUP in an older version can result in an unsupported-function error, so use VLOOKUP, HLOOKUP, or another compatible approach when recipients must use those releases.

VLOOKUP and HLOOKUP have been available in many older Excel versions, which is one reason they remain common in shared workbooks. Function availability is generally the same across current desktop platforms, though the interface and some editing details can differ between Windows, Mac, and Excel for the web. The formulas shown here use commas as argument separators; regional settings may require semicolons in some installations.

Frequently Asked Questions

Is XLOOKUP better than VLOOKUP?
XLOOKUP is more flexible for many new formulas because it uses separate lookup and return ranges, defaults to exact matching, and can return results from either side. VLOOKUP may be preferable when a workbook must work in an older Excel version that does not support XLOOKUP.

Can XLOOKUP replace HLOOKUP?
Yes. XLOOKUP can search a horizontal row and return a value from a corresponding row. Set the lookup array to the row containing the keys and the return array to the row containing the results. It can also search vertical ranges.

Why does VLOOKUP return the wrong value?
Check whether the last argument is omitted or TRUE, which enables approximate matching and depends on sorted lookup values. Also verify the table’s first column, the result-column number, duplicate keys, and whether the lookup and source values have compatible data types.

Does XLOOKUP work in Excel 2019?
No. XLOOKUP requires Microsoft 365 or Excel 2021 or later. If someone needs to open the workbook in Excel 2019, use a compatible formula such as VLOOKUP where the data layout permits it.

Can VLOOKUP look to the left?
Not directly: its lookup value must be in the leftmost column of the selected table, and it returns a value from a column to the right. XLOOKUP can return a value from a range on either side of its lookup range.

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.