XLOOKUP Examples That Replace VLOOKUP Pain for Good

VLOOKUP has been the workhorse of spreadsheets for thirty years, and nearly everyone who uses it has a story about the day it quietly returned the wrong value. The column number was off after someone inserted a column. The last argument was missing and the lookup did an approximate match on unsorted data. The key sat to the left of the value, so it could not be found at all. XLOOKUP fixes those faults by design. This article shows six patterns that cover most of what people use lookups for, and what to watch for with each. It is part of the toolkit in Excel formulas data people use every week, and the SQL view of the same operation is in VLOOKUP and XLOOKUP as SQL joins.

The syntax in one line

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

Only the first three arguments are required. lookup_array is where to search and return_array is where to read the answer from, and they must be the same size. The rest are optional switches, which the examples introduce one at a time.

Sample data for the examples, on a sheet called Customers:

Example 1: a basic exact lookup

Find the name for an ID in cell F2:

=XLOOKUP(F2, Customers!A:A, Customers!B:B)

If F2 holds 1002, the result is Bolt GmbH. The match is exact by default. If F2 holds a key that is not in column A, the result is #N/A.

Example 2: a friendly message for missing keys

=XLOOKUP(F2, Customers!A:A, Customers!B:B, "unknown customer")

The fourth argument replaces #N/A with your text. This is better than wrapping in IFERROR, because IFERROR also swallows genuine errors such as #REF! from a deleted column, while the fourth argument only handles "not found". Choose a value that downstream formulas can recognize: "" for a blank, 0 for a number, or a marker you can filter on.

Example 3: look to the left

VLOOKUP can only return columns to the right of the key. XLOOKUP does not care:

=XLOOKUP("Bolt GmbH", Customers!B:B, Customers!A:A)

This returns 1002, the ID from the column to the left of the name. Before XLOOKUP, this needed INDEX with MATCH. Be careful with name keys, because names are rarely unique, and XLOOKUP returns the first match.

Example 4: return several columns at once

Give return_array a block of columns and the formula spills across them:

=XLOOKUP(F2, Customers!A:A, Customers!B:D)

For 1002 this returns Bolt GmbH, DE and 8000 in three adjacent cells. One formula replaces three VLOOKUPs with different column numbers. The cells to the right must be empty or the result shows #SPILL!. If you need only some columns in a different order, use CHOOSECOLS on the return array or write separate formulas.

Example 5: a two-way lookup

Find a value by row and column headers. Suppose a price table has products down column A and sizes across row 1, with prices inside. Look up product in G2 and size in H2:

=XLOOKUP(G2, A2:A20, XLOOKUP(H2, B1:F1, B2:F20))

The inner XLOOKUP finds the size in the header row and returns the whole column of prices for that size. The outer one then picks the row for the product from that column. It replaces the older INDEX(MATCH(), MATCH()) pattern, and it reads in the order a person thinks. It also works when the headers are text or numbers, as long as they are unique.

Example 6: approximate matches and tiers

Commission or tax tiers use "the largest threshold not exceeding the value". With a tier table of thresholds in A2:A6 and rates in B2:B6:

=XLOOKUP(D2, A2:A6, B2:B6, 0, -1)

The fifth argument, match_mode of -1, means "exact match or the next smaller item". A sale of 12,500 against thresholds 0, 5,000, 10,000 and 20,000 returns the rate for 10,000. 1 means the next larger item. Unlike the old approximate VLOOKUP, the lookup array does not have to be sorted ascending, though sorted data searches faster with search_mode 2 (binary ascending). Always supply the first threshold at zero or below, or values under the lowest threshold return the not-found value.

Other match modes: 0 is exact (the default), 2 allows wildcards. With wildcards:

=XLOOKUP("*"&F2&"*", Customers!B:B, Customers!A:A, "none", 2)

finds the first name containing the text in F2. Wildcard matching is case-insensitive. Characters *, ? and ~ in the lookup value have special meanings and need a leading ~ to be taken literally.

The last match, and searching backwards

search_mode controls direction. 1 searches first to last (the default). -1 searches last to first, so the last match wins:

=XLOOKUP(F2, Log!A:A, Log!C:C, "no entry", 0, -1)

For a log sorted by time, with one row per event per customer, this returns the customer's latest status. It is the quick way to get "the most recent value for each key" without a pivot or a helper column.

Lookups that go wrong, and why

Type mismatch. The key is the text "1002" and the table holds the number 1002, or the reverse. XLOOKUP does not coerce, so it returns not found. Convert one side: --F2 turns text into a number, and TEXT(A2, "0") turns a number into text (fixing numbers stored as text).

Hidden characters. A trailing space or a non-breaking space makes two visually identical keys different. Clean the key with TRIM and SUBSTITUTE(x, CHAR(160), " ") first (trimming spaces and non breaking spaces).

Duplicates in the lookup range. XLOOKUP returns the first (or last) match and says nothing about others. If a key must be unique, test: =COUNTIF(Customers!A:A, F2) > 1 flags the problem. A SQL join would multiply rows instead.

Mismatched sizes. The lookup and return arrays must have the same number of rows, or the formula returns #VALUE!. Whole-column references on both sides avoid it.

Case. The match is case-insensitive. If you need case sensitivity, combine EXACT with FILTER or INDEX.

Performance on big ranges

On a few thousand rows nothing matters. On hundreds of thousands, with thousands of lookups, speed matters. Bound the ranges to the data or a table (Customers[id]) and not whole columns. For many lookups against the same table, use search_mode 2 on sorted data for binary search. For very large joins, a helper column that computes the match once and reuse it, or move the join to the database (slow Excel formulas over many rows).

When XLOOKUP is not available or not enough

XLOOKUP is missing from Excel 2019 and earlier, and in the Excel 2021 and Microsoft 365 builds that predate its release. A file that uses it opens with #NAME? for those readers. Use INDEX with MATCH for a portable exact lookup:

=INDEX(Customers!B:B, MATCH(F2, Customers!A:A, 0))

XLOOKUP is not enough when you must join many rows to many rows, when the lookup table is large enough to make recalculation slow, or when the lookup logic is shared across many workbooks. At that point, load both tables into a database and join them (importing Excel into PostgreSQL step by step shows the move). Microsoft documents the function in the XLOOKUP function reference, and the LibreOffice help for XLOOKUP covers the version in that suite.