Excel XLOOKUP

XLOOKUP is a modern lookup function introduced in Excel 2019 and Microsoft 365. It was designed to replace and improve upon both VLOOKUP and HLOOKUP, while also offering many capabilities that previously required the complex INDEX-MATCH combination. XLOOKUP is more flexible, simpler to write, and handles errors and missing values more gracefully.

What is XLOOKUP?

XLOOKUP searches a range for a value and returns a corresponding result from another range. Unlike VLOOKUP, the lookup column and the return column do not need to be in the same table — they are specified independently. XLOOKUP can also search horizontally, vertically, or in reverse order.

Syntax

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

• lookup_value: The value to search for.
• lookup_array: The range to search in (one column or one row).
• return_array: The range from which to return the result (can be multiple columns).
• [if_not_found]: Optional. Value to return if no match is found (replaces the need for IFERROR).
• [match_mode]: Optional. 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match.
• [search_mode]: Optional. 1 = first to last (default), -1 = last to first, 2 = binary search ascending, -2 = binary search descending.

Basic XLOOKUP Example

Data Table

  | A   | B     | C    | D      |
1 | ID  | Name  | Dept | Salary |
2 | 101 | Alice | Sales| 50000  |
3 | 102 | Bob   | IT   | 60000  |
4 | 103 | Carol | HR   | 45000  |

Find Salary by Employee ID

  =XLOOKUP(102, A2:A4, D2:D4)

  → Searches for 102 in column A (A2:A4)
  → Returns the corresponding value from column D (D2:D4)
  Result: 60000

Compared to VLOOKUP

  VLOOKUP version: =VLOOKUP(102, A2:D4, 4, FALSE)
  XLOOKUP version: =XLOOKUP(102, A2:A4, D2:D4)

  XLOOKUP is shorter, clearer, and the return column is specified directly
  — no need to count column numbers.

XLOOKUP with If Not Found

The fourth argument of XLOOKUP allows specifying a custom message when the lookup value is not found. This replaces the need to wrap formulas in IFERROR.

Example

  =XLOOKUP(105, A2:A4, B2:B4, "Employee Not Found")

  → 105 is not in the data
  Result: "Employee Not Found"

  Without this argument, XLOOKUP would return a #N/A error.

XLOOKUP Searching Left (Reverse Lookup)

VLOOKUP can only search the leftmost column and return values to the right. XLOOKUP has no such restriction — the lookup column and return column are completely independent.

Example: Return ID from Name

  Find the ID (column A) when given the Name "Carol" (column B):

  =XLOOKUP("Carol", B2:B4, A2:A4)
  Result: 103

  This is a left-side lookup — something VLOOKUP cannot do.

XLOOKUP Returning Multiple Columns

XLOOKUP can return an entire row of data from the return array — not just one column. This is useful for displaying a complete record based on a single lookup.

Example

  Return Name, Department, and Salary for ID 102:

  =XLOOKUP(102, A2:A4, B2:D4)

  → Finds 102 in column A
  → Returns the full row from B2:D4 (Name, Dept, Salary)
  Result: Bob | IT | 60000  (three values returned side by side)

XLOOKUP with Wildcard Match

Setting match_mode to 2 enables wildcard characters in the lookup value.

Wildcard Characters

• * — Matches any number of characters.
• ? — Matches any single character.

Example

  Find the salary of anyone whose name starts with "Al":

  =XLOOKUP("Al*", B2:B4, D2:D4, "Not Found", 2)

  → Wildcard match mode (2) is used
  → "Al*" matches "Alice"
  Result: 50000

XLOOKUP Searching in Reverse (Last Match)

By default, XLOOKUP finds the first matching entry. Setting search_mode to -1 makes it find the last matching entry.

Example: Find the Most Recent Transaction

  | A    | B       | C      |
1 | Date | Customer| Amount |
2 | Jan  | Alice   | 200    |
3 | Feb  | Bob     | 350    |
4 | Mar  | Alice   | 500    |
5 | Apr  | Alice   | 150    |

  Find Alice's most recent (last) transaction:
  =XLOOKUP("Alice", B2:B5, C2:C5, "Not Found", 0, -1)

  → search_mode = -1 searches from bottom to top
  Result: 150  (Alice's most recent entry, row 5)

XLOOKUP with Approximate Match

Match mode -1 returns the nearest value that is less than or equal to the lookup value. This is useful for tax brackets, grade thresholds, and commission rates.

Example: Grade Lookup

  Grade table:
  | A (Min Score) | B (Grade) |
  | 0             | F         |
  | 50            | D         |
  | 60            | C         |
  | 70            | B         |
  | 80            | A         |

  Find grade for a score of 73:
  =XLOOKUP(73, A2:A6, B2:B6, , -1)

  → match_mode = -1: finds the nearest value ≤ 73
  → 70 is the closest value ≤ 73
  Result: "B"

Nested XLOOKUP (Two-Way Lookup)

Two XLOOKUP functions can be nested to perform both row and column lookups simultaneously.

Example

  | A     | B    | C    | D    |
1 |       | Jan  | Feb  | Mar  |
2 | North | 4000 | 5000 | 4500 |
3 | South | 6000 | 5500 | 7000 |
4 | East  | 3000 | 4000 | 3500 |

  Find sales for "South" in "Feb":
  =XLOOKUP("South", A2:A4, XLOOKUP("Feb", B1:D1, B2:D4))

  → Inner XLOOKUP finds the "Feb" column and returns all values in that column
  → Outer XLOOKUP then finds "South" in that column result
  Result: 5500

XLOOKUP vs VLOOKUP vs INDEX-MATCH

  Feature                    | VLOOKUP | INDEX-MATCH | XLOOKUP
  ---------------------------|---------|-------------|--------
  Look left of search column | No      | Yes         | Yes
  Return multiple columns    | No      | No          | Yes
  Built-in error message     | No      | No          | Yes
  Wildcard search            | No      | No          | Yes
  Reverse search             | No      | Partial     | Yes
  Ease of writing            | Medium  | Complex     | Easiest

Summary

• XLOOKUP is the modern replacement for VLOOKUP, HLOOKUP, and INDEX-MATCH.
• It can search any column and return values from any other column — including to the left.
• The if_not_found argument handles missing values without needing IFERROR.
• It can return multiple columns at once and perform wildcard or approximate match lookups.
• Setting search_mode to -1 searches from the last entry upward, finding the most recent match.
• Nested XLOOKUP replaces the need for INDEX-MATCH-MATCH in two-way lookups.
• XLOOKUP is available in Excel 2019, Excel 2021, and Microsoft 365.