XLOOKUP

XLOOKUP arrived in Microsoft 365 in 2019 and immediately made VLOOKUP and HLOOKUP look like legacy tools. It addresses every limitation we covered in Lesson 15 — no leftmost-column restriction, no fragile column numbers, built-in error handling, backwards searching, and horizontal lookups all in one function. If you are on Microsoft 365, this is the lookup function you should be reaching for by default.

XLOOKUP Syntax — Clean and Logical

XLOOKUP separates the lookup range and return range into two distinct arguments. This is the key architectural improvement over VLOOKUP — you tell Excel exactly where to look and exactly where to return from, independently.

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

  lookup_value   →  What you are searching for
  lookup_array   →  The column (or row) to search in
  return_array   →  The column (or row) to return from
  if_not_found   →  What to show if no match (replaces IFERROR wrapping)
  match_mode     →  0 = exact, -1 = exact or next smaller, 1 = exact or next larger
  search_mode    →  1 = first to last (default), -1 = last to first, 2 = binary asc

Basic XLOOKUP — Exact Match

The simplest XLOOKUP uses just the first three arguments. You give it a value to find, the column to look in, and the column to return from. That is all you need for the most common lookup scenario.

Look up salary for employee E002:
=XLOOKUP("E002", A2:A6, D2:D6)             →  £61,000

With built-in error handling (4th argument):
=XLOOKUP("E009", A2:A6, D2:D6, "Not Found")  →  "Not Found"

Look up department by NAME instead of ID (any column works):
=XLOOKUP("James Smith", B2:B6, C2:C6)      →  "Finance"

Notice two things: the return column D is defined directly as a range — no column number to count. And the error text "Not Found" sits right inside the formula as the fourth argument — no IFERROR wrapper needed.

Returning Multiple Columns at Once

One of XLOOKUP's standout features is the ability to return an entire range of columns in a single formula. Because the return_array can be a multi-column range, one XLOOKUP can populate several fields simultaneously — spilling results across adjacent cells automatically.

Return Name, Department AND Salary in one formula:
=XLOOKUP("E002", A2:A6, B2:D6)

This spills three values across three cells:
  →  James Smith  |  Finance  |  £61,000

Compare to VLOOKUP — you would need three separate formulas:
  =VLOOKUP("E002", A:D, 2, FALSE)   ← Name
  =VLOOKUP("E002", A:D, 3, FALSE)   ← Department
  =VLOOKUP("E002", A:D, 4, FALSE)   ← Salary

This spill behaviour (returning multiple values from one formula) is part of Excel 365's dynamic array engine — the same engine behind FILTER, SORT, and UNIQUE. The formula only goes in the first cell; Excel fills the adjacent cells automatically.

Left-Direction and Reverse Lookup

Because lookup_array and return_array are completely independent, XLOOKUP handles reverse lookups — returning a value to the left of the search column — as naturally as a standard lookup. There is nothing special to do.

Look up the Employee ID from a Name (return left of lookup):
=XLOOKUP("James Smith", B2:B6, A2:A6)  →  "E002"

Column A (ID) is to the LEFT of column B (Name) —
VLOOKUP simply cannot do this. XLOOKUP does not care.

This is particularly useful when you are working with data where the natural identifier is not the first column — phone directories, supplier databases, and many exported reports have this structure.

Search Mode — Finding the Last Match

VLOOKUP always returns the first match it finds. XLOOKUP gives you control over search direction via the sixth argument — you can tell it to search from the bottom up instead, returning the most recent match. This is invaluable for transaction histories and audit logs.

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

search_mode values:
   1  →  Search first to last (default)
  -1  →  Search last to first (finds most recent match)
   2  →  Binary search ascending (faster on very large sorted tables)
  -2  →  Binary search descending

Find the most recent transaction for customer C005:
=XLOOKUP("C005", A2:A100, C2:C100, "No transactions", 0, -1)
  search_mode -1 returns the LAST match in the list

Match Mode — Approximate and Wildcard Matching

The fifth argument controls how XLOOKUP compares values. Exact match (0) is the default. The other options cover the scenarios where an exact match is either not available or not what you want.

match_mode values:
   0  →  Exact match (default — use this for most lookups)
  -1  →  Exact match, or next smaller value if not found
   1  →  Exact match, or next larger value if not found
   2  →  Wildcard match (* ? ~ supported)

Commission tier example (no exact match needed — use -1):
  Tier table:   0 → 5%,  50000 → 8%,  100000 → 12%,  150000 → 15%
  Sales = 93,000 — no exact match exists

=XLOOKUP(93000, A2:A5, B2:B5, , -1)
  →  8%   (93,000 rounds down to the nearest tier: 50,000)

Wildcard search — find any product containing "Pro":
=XLOOKUP("*Pro*", A2:A20, B2:B20, "Not found", 2)

The approximate match modes (-1 and 1) replace the TRUE argument in VLOOKUP for tier-based lookups — and unlike VLOOKUP, the table does not need to be sorted in ascending order for match_mode -1 and 1 to work reliably.

Nested XLOOKUP — Two-Way Lookup

Just like INDEX/MATCH/MATCH, you can nest two XLOOKUPs to perform a two-way lookup — finding a value at the intersection of a row and column. The inner XLOOKUP finds the right column header; the outer XLOOKUP uses that result to pull the value.

Freight rate table — same as Lesson 15 example:

         0-5kg   5-20kg   20kg+
North    £4.50   £8.00    £14.00
South    £5.00   £9.00    £16.00
East     £4.00   £7.50    £13.00

=XLOOKUP("South", A2:A4, XLOOKUP("5-20kg", B1:D1, B2:D4))

Inner XLOOKUP: finds "5-20kg" in headers → returns column {£8,£9,£7.50}
Outer XLOOKUP: finds "South" in row headers → returns £9.00

The nested XLOOKUP pattern is more readable than INDEX/MATCH/MATCH once you understand how it works. The inner XLOOKUP returns the entire matched column as an array, and the outer XLOOKUP then searches within that.

XLOOKUP vs VLOOKUP vs INDEX/MATCH

Next up — XMATCH, the modern replacement for MATCH that pairs perfectly with INDEX and unlocks position-based calculations you could not do before.