XLOOKUP Examples: 7 Real-World Formulas You Can Copy

XLOOKUP replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH combos with one function — but its five optional arguments are where the real power hides. These seven examples cover the situations that come up in real spreadsheets: friendly error messages, tax-bracket tiers, finding the latest entry, horizontal tables, two-way matrix lookups, and wildcard searches.

Example 1: What does a basic exact-match XLOOKUP look like?

Unlike VLOOKUP, XLOOKUP defaults to exact match — no trailing FALSE needed — and the return column can be anywhere, including left of the lookup column. That alone eliminates the two most common VLOOKUP mistakes, as we cover in VLOOKUP vs XLOOKUP.

' Find the price for the product ID in E2
=XLOOKUP(E2, A2:A200, C2:C200)

Example 2: How do I replace #N/A with a friendly message?

The fourth argument, if_not_found, returns whatever you specify when there is no match — no IFERROR wrapper required. Use it for human-readable placeholders or a 0 you can safely sum.

=XLOOKUP(E2, A2:A200, C2:C200, "Not in catalog")

' Numeric fallback so downstream SUMs still work
=XLOOKUP(E2, A2:A200, C2:C200, 0)

Example 3: How do I do tiered lookups like tax brackets?

match_mode -1 means "exact match or next smaller item" — exactly what tiered rates need. List the lower bound of each bracket in ascending order, and XLOOKUP snaps each income down to its bracket. No more sorting anxiety or off-by-one bracket errors.

' A2:A5 = 0, 11000, 44725, 95375   (bracket lower bounds)
' B2:B5 = 10%, 12%, 22%, 24%       (rates)

=XLOOKUP(E2, A2:A5, B2:B5, , -1)

' E2 = 50000  →  returns 22% (snaps down to 44725)

Use match_mode 1 ("next larger") for the reverse case, such as shipping weight bands defined by their upper limits.

Example 4: How do I find the LAST matching entry?

search_mode -1 searches from the bottom of the range upward, so the first hit is the most recent row. This is the clean way to pull a customer's latest order from a chronologically appended log — a task that used to need an ugly LOOKUP(2,1/...) trick.

' Latest order amount for the customer in E2
' (orders appended to the bottom as they happen)
=XLOOKUP(E2, A2:A5000, C2:C5000, "No orders", 0, -1)

Example 5: Can XLOOKUP work horizontally?

Yes — XLOOKUP does not care about orientation. Point it at a row instead of a column and it fully replaces HLOOKUP. Great for month-per-column budget layouts.

' B1:M1 = Jan..Dec, B5:M5 = monthly revenue
=XLOOKUP("Jul", B1:M1, B5:M5)

Example 6: How do I do a two-way (matrix) lookup?

Nest one XLOOKUP inside another: the inner one finds the right column and returns it as an array, the outer one finds the right row within it. This replaces the classic INDEX/MATCH/MATCH pattern with something you can read aloud.

' Rows = products (A2:A20), Columns = regions (B1:F1)
' Body = prices (B2:F20)
=XLOOKUP(H2, A2:A20,
    XLOOKUP(I2, B1:F1, B2:F20))
' H2 = product, I2 = region

To match on two criteria in the same column direction instead (e.g. name + date), concatenate arrays as shown in XLOOKUP with multiple criteria.

Example 7: How do wildcard matches work in XLOOKUP?

Set match_mode 2 and XLOOKUP honors * (any characters) and ? (one character) in the lookup value. Handy for partial invoice references or messy vendor names. Wildcards are ignored unless you explicitly set mode 2, which prevents accidental fuzzy matches.

' Find the first company containing the text in E2
=XLOOKUP("*"&E2&"*", A2:A300, B2:B300, "No match", 2)

' To match a literal * or ?, escape it with a tilde: ~* or ~?

Which arguments matter most in practice?

Ninety percent of real formulas use just the first four arguments — value, lookup array, return array, and if_not_found. Reach for match_mode -1 whenever the words "bracket", "tier", or "band" appear in the requirement, and search_mode -1 whenever you need the newest record from a log. Keep ranges sized to the data rather than whole columns: every extra empty row is work XLOOKUP does on each recalculation.