XLOOKUP explained: how to reliably look up values, truthfully handle missing entries, and translate every pattern back to INDEX/MATCH for older versions of Excel.

FAQ — What does this article answer?

Q: What is XLOOKUP and why should I use it instead of VLOOKUP?

A: XLOOKUP searches one range and returns the matching value from another. Unlike VLOOKUP, it can search from left to right as well as right to left. It does not break when a column is inserted. It defaults to an exact match rather than an approximate one. It has a built-in argument for the 'not found' case. Every pattern below is safer with XLOOKUP than with VLOOKUP.

Q: What are the six arguments of XLOOKUP?

A: lookup_value, lookup_array, return_array, if_not_found, match_mode and search_mode. The first three are required and the last three are optional. Most users never get past the third argument, but it is here that the useful functionality begins.

Q: How do I handle a lookup that finds nothing?

A: Use the fourth argument, if_not_found. This is safer than wrapping the whole formula in IFERROR, because it only catches missing values and leaves every other error visible.

Q: How can I look up a value within a band, such as a tax bracket, discount tier or mileage rate?

A: Use match_mode -1 (exact match or the next smaller value) against a table of band thresholds. This is the correct replacement for VLOOKUP(...,TRUE), and unlike VLOOKUP it does not silently return the wrong answer when the table is unsorted.

Q: Can XLOOKUP return more than one cell?

A: Yes. If return_array is a two-dimensional range, XLOOKUP returns the entire matching row or column and spills it. One formula replaces five. It can also return a reference, meaning that two XLOOKUP calls can define the start and end of a dynamic range.

Q: How do I look up by row and column at the same time?

A: Nest one XLOOKUP inside another. The inner call selects the column and the outer call selects the row. This replaces the older INDEX(range, MATCH(...), MATCH(...)) construction.

Q: I have Excel 2016 or 2019, but XLOOKUP is not available. What should I use instead?

A: INDEX combined with MATCH. Part 7 provides an exact equivalent for each pattern in this article, including banded and two-way lookups.

Almost every important workbook contains a lookup table. For example, a price list is joined to an order table. An employee number linked to a rate, for example. Or a cost centre joined to a department name. This is the most common formula task in Excel, and for about twenty-five years it was also the most common source of incorrect results.

VLOOKUP is responsible for most of that history. It counts columns instead of naming them, so inserting a column can break the formula unexpectedly. Its fourth argument defaults to approximate matching, so if this is left off, a plausible neighbouring value is returned rather than an error. It cannot look to the left of the search column. Furthermore, it has no way to specify what should happen when nothing is found.

XLOOKUP fixes all four of these problems. This article covers its full syntax, the lesser-known arguments, the patterns that make it worth learning properly and a complete translation table back to INDEX/MATCH for those still using Excel 2016 or 2019, which is still the case for a large proportion of desktops in German SMEs.

Part 1: The four problems XLOOKUP solves

Consider a rate table on a sheet called Rates, formatted as an Excel table with columns ID, Name, Department and Rate.

The classic formula:

=VLOOKUP(A2, Rates[#All], 4, FALSE)

This works, until one of these happens:

The column-index problem is important because it occurs without any indication. A #REF! error indicates that something has gone wrong. However, a formula that reads the Department column instead of the Rate column does not provide any information — it simply returns text where a number should be, or worse, a seemingly legitimate number. This is the same class of failure discussed in How to handle errors in Excel formulas.: the dangerous errors are the ones that never appear.

Part 2: The full syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The diagram above shows all six arguments, as well as the same lookup running on a three-column table. The value in cell A2 is found in the lookup array and the aligned cell in the return array is returned as the result.

The simplest form:

=XLOOKUP(A2, Rates[ID], Rates[Rate])

Read the following aloud: Find the value in A2 within the ID column and give me the matching value from the Rate column. There is no need to remember a column number or ordering requirement, nor is there a fourth argument. Even if someone were to insert three columns into the middle of the table tomorrow, the formula would still work because the structured references move with the columns. This is one of several reasons to format every dataset as a table. See Why every dataset should be an Excel table..

Looking leftwards requires no special handling:

=XLOOKUP(A2, Rates[Rate], Rates[Name])

The two ranges are merely arguments. Neither has to be to the left or right of the other. They just need to be the same length. If they are not, XLOOKUP returns #VALUE! rather than making an estimate.

Part 3: if_not_found, the argument that replaces IFERROR

Most lookup formulas in the wild look like this:

=IFERROR(XLOOKUP(A2, Rates[ID], Rates[Rate]), 0)

This is a habit that has been carried over from VLOOKUP, which had no other option. However, it should not be carried over to XLOOKUP, because the fourth argument performs the same function with far greater precision:

=XLOOKUP(A2, Rates[ID], Rates[Rate], 0)

The difference is not cosmetic. 'IFERROR' catches every error that the formula can produce, whereas 'IF_NOT_FOUND' only catches one condition: the lookup value was not present in the lookup array. Anything else, such as a mismatched range length producing a #VALUE!, a deleted column producing a #REF!, or a typo producing a #NAME?, still surfaces as a visible error that you can find and fix.

This is an example of a failed attempt to make the point concrete. Suppose the Rate column contains a value that has been copied from a PDF and stored as text. With the IFERROR version, that record silently becomes 0, resulting in an understated payroll total with no visible sign. With the if_not_found version, however, the underlying problem still comes to light.

Useful values for the fourth argument:

=XLOOKUP(A2, Rates[ID], Rates[Rate], "")          ' blank cell in a finished report
=XLOOKUP(A2, Rates[ID], Rates[Rate], 0)           ' zero, when zero is genuinely correct
=XLOOKUP(A2, Rates[ID], Rates[Rate], "not found") ' explicit marker for review
=XLOOKUP(A2, Rates[ID], Rates[Rate], NA())        ' keep #N/A deliberately, e.g. to exclude from charts

It's worth knowing that last one. Charts ignore #N/A but plot zeros. If you replace missing data points with 0, your line chart will drop to the axis. If you leave them as #N/A, there is simply a gap in the line.

One note of caution: an empty matching cell is not the same as a missing match. If the ID exists but the rate cell is blank, XLOOKUP returns 0, not the if_not_found value, because it did find the record. To distinguish between these two cases, test explicitly.

=LET(
    rate, XLOOKUP(A2, Rates[ID], Rates[Rate], "no record"),
    IF(rate = 0, "rate missing", rate)
)

LET avoids running the lookup twice. See LET: how to write cleaner, faster formulas.

Part 4: match_mode, banded lookups, done safely

The fifth argument is where XLOOKUP earns its place in serious financial models.

Exact match (0, the default)

Nothing to configure. This is what you want for IDs, account numbers, article codes and names.

The banded lookup: exact or the next smaller value (-1)

This pattern applies to tax brackets, discount tiers, volume rebates, commission bands, shipping-weight prices and mileage rates. You enter the lower threshold of each band and Excel identifies the relevant band.

There is a discount table on a sheet called Tiers:

=XLOOKUP(B2, Tiers[Threshold], Tiers[Discount], , -1)

With an order value of 7,400, there is no exact match, so XLOOKUP takes the next smaller threshold of 5,000 and returns 5%. Note the empty fourth argument, marked by the two commas: if_not_found is skipped, meaning that values below the lowest threshold produce a visible #N/A error rather than a zero.

This is the critical difference from VLOOKUP(..., TRUE): VLOOKUP's approximate mode assumes the table is sorted in ascending order and returns nonsense without any error if it is not. With match_mode = -1 and the default search_mode = 1, XLOOKUP scans the range properly and does not require sorted data. It is the difference between a rule that is enforced and a rule that is merely hoped for.

Exact or next larger (1)

The mirror image. This is used when a band is defined by its upper limit, such as a delivery-time SLA, a "up to and including" pricing table or a capacity band.

=XLOOKUP(B2, Limits[UpTo], Limits[Price], , 1)

Wildcard match (2)

Enables * (any number of characters), ? (any single character) and ~ (escape the next character).

=XLOOKUP("*GmbH", Customers[Name], Customers[ID], "no match", 2)

This is useful for messy imported name columns, but treat it as a diagnostic tool rather than a production formula. Wildcards return the first match, and 'first' in a customer list is rarely a meaningful concept. If you find yourself relying on it, the real solution is usually to address the issue at an earlier stage. See How to clean messy data in Excel..

Part 5: search_mode, direction and speed

The sixth argument controls how the range is traversed.

The -1 mode is the one worth memorising. Given a transaction log sorted by date with several rows per customer, the last matching row is usually the current one:

=XLOOKUP(A2, Log[Customer], Log[Status], "no history", 0, -1)

This returns the most recent status for the customer, rather than the oldest. Before XLOOKUP, achieving the same thing required either a sorted helper column or an array formula, which most colleagues could not maintain.

The binary modes are a performance option, not a correctness option. They require the data to be genuinely sorted and will fail silently if it is not, reintroducing precisely the risk of using VLOOKUP(..., TRUE) that XLOOKUP was designed to eliminate. Only use them on ranges of many thousands of rows where you control the sort order and document this requirement in the sheet.

Part 6: Returning whole rows, and returning references

One formula instead of five

If return_array is two-dimensional, XLOOKUP returns the entire matching row and spills it across the neighbouring cells:

=XLOOKUP(A2, Rates[ID], Rates[[Name]:[Rate]])

This returns Name, Department and Rate all at once as a spilled range. Rather than carrying out three or five separate lookups, each of which re-scans the ID column, you only need to carry out one. This makes it faster to recalculate, and there is only one formula to maintain. The behaviour follows the spill rules described in Dynamic arrays — how FILTER, SORT, UNIQUE and SEQUENCE work together.

If you want the columns in a different order, or only some of them, combine with CHOOSECOLS:

=CHOOSECOLS(XLOOKUP(A2, Rates[ID], Rates[[Name]:[Rate]]), 3, 1)

XLOOKUP returns a reference, not just a value

This is the least well-known yet most powerful property of the function. Since XLOOKUP returns an actual cell reference, two calls can define the two ends of a range.

=SUM(XLOOKUP("March", Months, Amounts):XLOOKUP("August", Months, Amounts))

This sums everything from the March cell to the August cell inclusive, creating a genuinely dynamic range defined by two labels rather than two hard-coded addresses. Change the month names in the driver cells and the summed range will move with them.

This is an effective replacement for the OFFSET and INDIRECT constructions used by older workbooks for dynamic ranges. However, both of these functions are volatile, forcing recalculation of everything that depends on them on every edit anywhere in the workbook. XLOOKUP is not volatile. Replacing volatile range-building with this pattern is one of the largest single performance gains available on a model of any size. See Volatile functions: what they are and when to avoid them.

Combined with cell references for the endpoints:

=SUM(XLOOKUP($B$1, Months, Amounts):XLOOKUP($B$2, Months, Amounts))

With B1 and B2 as dropdowns, this becomes a period selector with no VBA and no helper columns. Building the dropdowns is covered in Data validation, dropdowns and dependent lists.

Part 7: Two-way lookup of rows and columns simultaneously

When working with a matrix of monthly figures where products are listed down the left-hand side and months are listed across the top, it is often necessary to find the cell where a chosen product meets a chosen month.

=XLOOKUP(B1, ProductNames, XLOOKUP(B2, MonthHeaders, DataMatrix))

Read it from the inside out. The inner XLOOKUP searches the header row and returns the entire matching column of the matrix. The outer XLOOKUP then searches the product names and returns the matching row of that column — a single cell.

This is more readable than the traditional INDEX(matrix, MATCH(...), MATCH(...)) formula because each step names its own ranges rather than producing bare position numbers. If you want it to be readable to a colleague six months from now, wrap it in LET:

=LET(
    product,   B1,
    month,     B2,
    column,    XLOOKUP(month, MonthHeaders, DataMatrix, "unknown month"),
    XLOOKUP(product, ProductNames, column, "unknown product")
)

Each failure mode now has its own message, so a wrong result tells you which of the two selections was invalid.

Part 8: The INDEX/MATCH fallback for Excel 2016 and 2019

XLOOKUP requires Excel 365 or Excel 2021. It is not available in Excel 2016 or 2019; a workbook containing it will open in these versions with the error message _xlfn.XLOOKUP and #NAME?. If your file is to be opened by anyone on a perpetual licence, which is still common across German SMEs and public bodies, you need the older construction.

The base pattern is:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

MATCH finds the position of the value and INDEX returns the item at that position. Together, they perform all the functions of VLOOKUP, in either direction, without the need for column numbers.

Complete translation table:

Two rows in that table carry a warning and are the reason to migrate when possible. Banded lookups using MATCH types 1 and -1 require sorted data and return the wrong answer silently when the sort is broken. XLOOKUP removes that issue, but INDEX/MATCH does not.

Note also the second row: with INDEX/MATCH, error handling reverts to an outer wrapper. Use IFNA there, not IFERROR, for the reasons set out in the error handling article.

XMATCH is also worth mentioning. It is a modernised version of MATCH, with the same availability in 365/2021, improved match_mode and search_mode arguments, and it returns a position rather than a value. Use it when you need the position itself, for example to supply an ÌNDEX` over several parallel ranges or to check whether a value exists.

Part 9: Common failure modes

The first row accounts for the vast majority of genuine support requests. Excel is correct to say it found nothing because a lookup value of "4711 " and a table entry of "4711" are different strings. Similarly, the number 4711 will not match the text "4711". Both of these issues should be addressed in the data-cleaning layer rather than in an increasingly defensive formula, as described in The four layers of a well-built workbook.

Part 10: Version compatibility

If you are creating files to be shared outside your own machine, decide on the target version once and save the entire workbook to it. A mixed file, modern in some sheets and legacy in others, is the hardest kind of file to maintain. Version differences across the entire feature set are covered in Excel 365 versus older versions.

Summary

XLOOKUP is not simply a tidier VLOOKUP. The first three arguments replace it; the last three do things VLOOKUP never could.

• Use the default exact match for IDs, codes and names. Do not think about it again.
• Use the fourth argument, if_not_found, instead of wrapping the formula in IFERROR. Missing data gets handled; broken formulas stay visible.
• Use match_mode = -1 for every tax bracket, discount tier and rate band. It is the safe replacement for VLOOKUP(..., TRUE).
• Use search_mode = -1 to get the most recent record from a log.
• Return whole rows instead of writing four parallel lookups.
• Use two XLOOKUP calls as range endpoints to replace volatile OFFSET and INDIRECT constructions.
• On Excel 2016 or 2019, use INDEX/MATCH, and remember that its banded modes require sorted data and fail silently when that assumption breaks.

The underlying principle is the same as that which runs through every article in this series: a formula should produce an obvious error message when something goes wrong and should not require a comment to explain its purpose. XLOOKUP makes achieving both of these goals easier than any previous lookup function.

See also: How to handle errors in Excel formulas — IFERROR, IFNA and the IS-family for the error-handling layer around every lookup, Why every dataset should be an Excel table for the structured references that keep lookups stable, Dynamic arrays with FILTER, SORT, UNIQUE and SEQUENCE for the spill behaviour used in Part 6, and Volatile functions — what they are and when to avoid them for why the reference-returning trick matters on large models.

This article is part of the helpme.safeoffice.de series, which provides practical guides on Excel functions, workbook modeling and data solutions. The series is aimed at businesses that want effective, maintainable tools that everyone can understand.