Skip to content
en
Practical GuidesAll tools

Categories

Developer ToolsEncoding & DecodingJSON & DataExcel ToolsHash & ChecksumText ToolsDate & TimeNetwork ToolsFile ToolsMarkdown & Diagrams

Excel XLOOKUP Formula Generator

Enter the value to find, where to look and what to return, and get a working XLOOKUP plus versions for older Excel.

Processed locally in your browser

Options

Usually a cell such as A2. Text you type is quoted for you.

One column, or several columns such as D:F to return a whole row part.

Leave empty to get #N/A when nothing matches.

Excel uses ";" when the regional settings use a decimal comma. Decimal numbers are then written with a comma too.

What is Excel XLOOKUP Formula Generator?

XLOOKUP is the modern replacement for VLOOKUP: it searches one range and returns the item at the same position in another, it can look left, it has a built-in "if not found" argument and it does not break when columns are inserted. Its optional match and search modes are easy to get wrong, though, so this generator writes them for you.

Fill in the lookup value, the lookup range and the return range (a single column or several), then choose the not-found text, the match mode (exact, next smaller, next larger or wildcard) and the search mode (first to last, last to first or binary). The tool shows the formula, an explanation and, for workbooks that do not have XLOOKUP, a VLOOKUP version when it is possible and an INDEX and MATCH version, including the classic LOOKUP trick for the last match.

How does it work?

  1. Enter the lookup value, usually a cell such as A2, and the lookup range where that value is searched, such as Sheet2!A:A.
  2. Enter the return range that holds the answer (Sheet2!D:D, or D:F for several columns) and the text to show when nothing matches.
  3. Choose the match mode and search mode if you need more than an exact, first-to-last search.
  4. Copy the formula, or copy one of the equivalents if your Excel version lacks XLOOKUP. The separator and function language can be changed for regional versions.

Common use cases

  • Pulling a price, a name or a status from another sheet by ID without counting columns.
  • Looking up a value that sits to the left of the key column, which VLOOKUP cannot do.
  • Finding the last order of a customer with the last-to-first search mode.
  • Assigning tiers or tax bands with "exact or next smaller" on a sorted threshold table.

Examples

Enter the value to find, where to look and what to return, and get a working XLOOKUP plus versions for older Excel.

Privacy

Excel XLOOKUP Formula Generator runs entirely in your browser. The text or files you provide are processed on your device and are not uploaded, logged or stored on our servers.

Limitations

XLOOKUP exists only in Excel 2021, Microsoft 365 and Excel for the web. The generator does not read your workbook, so it cannot check that the ranges hold what you expect, and the VLOOKUP equivalent is possible only for simple, same-sheet ranges.

Frequently asked questions

What is the difference between XLOOKUP and VLOOKUP?

VLOOKUP needs a table array and a column number and only searches the first column, so it cannot look left and breaks when a column is inserted. XLOOKUP takes the lookup range and the return range separately, defaults to an exact match and has a built-in not-found argument.

What do the match modes do?

0 is an exact match, -1 returns the exact item or else the next smaller one, 1 returns the exact item or else the next larger one, and 2 allows the wildcards * and ? in the lookup value. The last two are useful for tax bands or price tiers on a table sorted by threshold.

How do I get the last match instead of the first?

Set the search mode to "last to first" (-1). XLOOKUP then scans from the bottom, which returns the most recent row for a customer or product. The tool also shows the LOOKUP(2,1/(range=value),result) trick that does the same in older versions.

Related tools

More tools in Excel Tools →