Excel Lookup Function Examples: XLOOKUP, VLOOKUP and More

Excel lookup functions find a value in one range and return a related value from another. For example, with product codes in A2:A5 and prices in B2:B5, enter a code in E2 and use =XLOOKUP(E2,A2:A5,B2:B5,"Not found") to return its price or display “Not found” if the code is absent. XLOOKUP is generally the most flexible choice in Microsoft 365, while VLOOKUP, INDEX/MATCH, HLOOKUP, and LOOKUP are useful for existing workbooks or particular layouts.

What an Excel lookup does

A lookup connects a search value to a corresponding result. A common example is finding a customer name from a customer ID, a product price from a stock code, or a department from an employee number. The source data is usually arranged in a table: one column contains values to search, and another contains the information to return.

Lookup formulas do not usually search for a value anywhere in a worksheet. They search a specified range or array, then return a value from a range related to it. Choosing the right ranges matters: the lookup range and return range must line up so that the result comes from the same record as the match.

For the examples below, imagine a product list with codes P101 through P104 in A2:A5 and the corresponding prices in B2:B5: $12.50, $18.00, $24.95, and $9.75. If E2 contains P103, an exact lookup should return $24.95. The prices are illustrative; a formula returns the value stored in the matched row, not a calculation of its own.

XLOOKUP: the flexible modern option

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It searches one range and returns a value from a second range, so the search and result ranges can be separate and can sit on either side of one another. It also uses exact matching by default, which is normally appropriate for product codes, IDs, and names.

With product codes in A2:A5, prices in B2:B5, and the requested code in E2, use =XLOOKUP(E2,A2:A5,B2:B5,"Not found"). If E2 contains P103, the result is $24.95; if E2 contains P999, the result is Not found. The fourth argument supplies a readable fallback instead of the usual not-found error.

The basic argument order is the value to find, the range to search, and the range to return from. The optional arguments can specify what to return when there is no match, a match mode, or a search direction. For example, match mode 0 means exact match, -1 means exact match or the next smaller item, 1 means exact match or the next larger item, and 2 enables wildcard matching. Exact match is already the default, so those options are only needed when a different behavior is intended.

XLOOKUP can also return values from a column to the left of the search column; VLOOKUP cannot do that directly. It can search horizontally as well as vertically by using row ranges. When duplicate lookup values exist, XLOOKUP returns the first match it encounters by default. Its search mode can be changed to search from last to first, which is useful when the latest matching record appears at the bottom of a list.

VLOOKUP: a familiar table lookup

VLOOKUP searches down the first column of a selected table and returns a value from another column in the same row. It is widely used in older workbooks and remains available in current Excel. Its main limitation is that the return column must be to the right of the lookup column, and the formula identifies that return column by its position within the selected table.

With product codes in A2:A5, product names in B2:B5, prices in C2:C5, and the requested code in E2, use =VLOOKUP(E2,A2:C5,3,FALSE). If E2 contains P103 and that row’s price in column C is $24.95, the result is $24.95. The 3 means return the third column of the selected table, and FALSE requests an exact match.

For codes and other identifiers, include FALSE rather than leaving the final argument out. If that argument is omitted or set to TRUE, VLOOKUP uses approximate matching, which expects the first column to be sorted in ascending order. An unsorted list can then produce a misleading answer rather than a clear warning. Another common maintenance problem is inserting or moving columns: the column number in the formula may no longer point to the intended result.

INDEX and MATCH: a flexible combination

INDEX and MATCH are often used together when a workbook needs to support older Excel versions or when users want a lookup that can return a value from either side of the search column. MATCH finds the position of a value in a range; INDEX returns the item at a specified position in another range. Using an exact-match setting in MATCH makes the intent clear.

With product codes in A2:A5, prices in B2:B5, and the requested code in E2, enter =INDEX(B2:B5,MATCH(E2,A2:A5,0)). If E2 contains P103, MATCH finds P103 in the third position of A2:A5, and INDEX returns the third price in B2:B5: $24.95. The 0 at the end of MATCH requests an exact match.

This structure is especially helpful when the result column is left of the lookup column: simply put the return range inside INDEX and the search range inside MATCH. It also avoids VLOOKUP’s numeric column index. The trade-off is that the formula combines two functions, so someone unfamiliar with the pattern may find it less immediately readable than XLOOKUP.

HLOOKUP: searching across a row

HLOOKUP works like VLOOKUP but searches across the first row of a table and returns a value from a specified row beneath it. It can suit a compact data layout where months, product types, or categories run horizontally across columns. For data stored in ordinary records, a vertical table and XLOOKUP or VLOOKUP are often easier to extend.

With month names in A1:D1, sales figures in A2:D2, and the month to find in E1, use =HLOOKUP(E1,A1:D2,2,FALSE). If A1:D1 contains January, February, March, and April, and A2:D2 contains 120, 150, 180, and 160, then E1 containing March returns 180. The 2 tells HLOOKUP to return a value from the second row of the selected range, and FALSE requires an exact match.

As with VLOOKUP, the search row must be the first row in the selected table, and the return row is specified by a position number. FALSE is a sensible choice for exact labels. For approximate matching, the first row must be sorted in ascending order.

LOOKUP: approximate matches and thresholds

LOOKUP is an older function that is useful for approximate matches, such as assigning a rating or band from a sorted list of cutoffs. In its vector form, it searches one row or column and returns the value at the corresponding position in another row or column. Its search values must be sorted in ascending order; it is not a good substitute for an exact-match lookup on an unsorted list of IDs.

Suppose score cutoffs 0, 60, 70, and 80 are in A2:A5, with labels Needs work, Pass, Good, and Excellent in B2:B5. With a score in E2, use =LOOKUP(E2,A2:A5,B2:B5). If E2 is 74, LOOKUP returns Good because 70 is the largest cutoff that does not exceed 74. A score of 80 returns Excellent. This example assumes the cutoff values are sorted in ascending order and the scores fall within the defined range.

For new formulas, XLOOKUP with an explicit approximate match mode can make the intended matching behavior clearer. LOOKUP can still be useful in legacy spreadsheets, but its approximate behavior is easy to misunderstand if a reader assumes it finds only exact values.

How to choose a lookup function

For most new work in Microsoft 365, start with XLOOKUP: its separate search and return ranges, exact-match default, and optional not-found result make common lookups straightforward. Use a different function when compatibility, existing workbook conventions, or a specific data shape calls for it.

Choose XLOOKUP for a new exact lookup, a lookup that must return from either side, or a formula with a custom not-found message. It requires Microsoft 365 or Excel 2021 and later.
Choose VLOOKUP when maintaining a familiar vertical lookup in an older workbook and the result column is to the right of the search column.
Choose INDEX/MATCH when compatibility with older Excel matters, or when a VLOOKUP-style result must come from the left of the lookup column.
Choose HLOOKUP when the lookup values are arranged across the top row and the result is below them.
Choose LOOKUP for a deliberate approximate lookup against sorted thresholds, especially when maintaining a workbook that already uses it.

Microsoft 365 on Windows and Excel for the web support XLOOKUP. On Mac, the function is available in Microsoft 365 and Excel 2021 or later as well. In the web version, formulas are entered in worksheet cells in the same way, though some ribbon details can differ from the Windows desktop application. If a file must be opened in a much older Excel release, check function compatibility before replacing a legacy formula with XLOOKUP.

Common lookup errors and how to fix them

A formula can be syntactically valid and still return an unexpected result. Check the data and ranges before rebuilding the formula; many lookup problems come from a mismatch between what is stored and what is being searched.

#N/A or a not-found result: Confirm that the lookup value exists and that the search range includes it. Check for leading or trailing spaces, misspellings, or different values such as the number 123 stored as text in one place and as a number in another.
Wrong VLOOKUP column: Recount the columns starting from the left edge of the table range. The index is relative to that range, not the worksheet’s column letters.
Unexpected approximate result: Check whether VLOOKUP or HLOOKUP is using approximate matching and whether the lookup row or column is sorted ascending. For exact identifiers, explicitly select FALSE in those functions.
Wrong record when duplicates exist: Standard lookups generally return one matching result, commonly the first one found. If the lookup key is not unique, include another field in the search design or use a method intended to return multiple records.
Formula changes after copying: A range may shift when the formula is filled down. Use absolute references such as $A$2:$A$5 for fixed source ranges, while leaving a changing input such as E2 relative when appropriate.

It is also worth checking whether values that look identical really match. Nonprinting spaces, inconsistent punctuation, and inconsistent use of text versus numbers can prevent an exact match. If a value is calculated from another formula, inspect the underlying result rather than relying only on its displayed formatting.

Lookup functions compared with newer alternatives

Lookup functions are designed to return related values, typically one result for a match. If the goal is to show every row that meets a condition, FILTER may be more suitable than a single lookup. FILTER is available in Microsoft 365 and Excel 2021 or later. For example, it can return several matching records rather than stopping at the first matching code. That is a different task from finding one price, so a lookup function is not always the right tool.

Sorting and filtering worksheet data are also useful when a person needs to inspect records manually, but they do not automatically create a formula-driven result in another cell. A lookup formula updates its result when its inputs or source data change, making it practical for dashboards, order forms, and summary sheets. For a larger workbook, consider turning the source range into an Excel table: structured references can make formulas easier to understand and can expand as records are added.

One misconception is that XLOOKUP is simply a newer spelling of VLOOKUP. The functions serve a similar purpose, but their arguments and capabilities differ. XLOOKUP takes the search range and return range directly; VLOOKUP uses a rectangular table and a column number. Another misconception is that approximate matching is inherently faster or preferable. Matching mode should reflect the data: exact identifiers generally need exact matching, while ordered thresholds may call for approximate matching.

Frequently Asked Questions

What is the most commonly used lookup function in Excel?
XLOOKUP is a strong default for new work in Microsoft 365 because it uses exact matching by default and can return values from either side of the search range. VLOOKUP remains common in older files and shared workbooks.

Is XLOOKUP better than VLOOKUP?
For many new formulas, XLOOKUP is more flexible: it does not require the return column to be to the right, and it can specify what to show when no match is found. VLOOKUP can still be appropriate when maintaining an existing workbook or supporting an older Excel version.

How do I look up a value to the left in Excel?
Use XLOOKUP with the search range and return range specified separately, or use INDEX/MATCH. VLOOKUP expects its lookup column to be the first column in the selected table and cannot directly return a column to its left.

Why does my lookup return #N/A?
The value may be missing, the search range may be wrong, or the lookup value and source value may differ because of spaces or text-versus-number formatting. For XLOOKUP, an optional not-found argument can display a clearer message, but it does not fix a mismatch in the underlying data.

Can Excel return more than one matching result?
A standard XLOOKUP or VLOOKUP returns a single match. In Microsoft 365 or Excel 2021 and later, FILTER can return multiple rows that meet a condition, so it is a better fit when all matching records are needed.

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.