Complete Guide to the XLOOKUP Function: Exact/Approximate Matches, Multiple Values, Reverse Search, and Binary Search

The XLOOKUP function is a modern lookup function that finds a value in a range or array and returns the result for the first matching item. It uses exact match by default, lets you specify an error message with if_not_found, and lets you control approximate matches, wildcards, reverse search, and binary search with match_mode and search_mode. (Official definition)

→ For a more detailed comparison, see XLOOKUP vs VLOOKUP.

Quick Fix: 2-Minute Setup (Exact Match + Message)

1. Enter a lookup value: for example, H2 = employee ID
2. Basic formula: =XLOOKUP(H2, $A:$A, $E:$E, "Not found")
3. Key point: Exact match is the default. If no match is found, it returns “Not found” to prevent #N/A.

Syntax and Arguments

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

• lookup_value: The value to find
• lookup_array: The range to search (column or row)
• return_array: The range to return (column or row)
• if_not_found: What to display when no match is found
• match_mode: 0 = exact match, -1 = exact match or next smaller item, 1 = exact match or next larger item, 2 = wildcard match
• search_mode: 1 = first to last, -1 = last to first, 2 = binary search (ascending), -2 = binary search (descending)

Understanding match_mode Quickly

• 0 (default): Exact match
• ±1: Approximate match (requires ascending or descending sorting)
• 2: *, ?, and ~ wildcards

Using search_mode Correctly

• -1: Reverse search (last value takes priority)
• ±2: Binary search — sorting is required, and unsorted data can produce incorrect results

7 Practical Examples You Can Reproduce

1) Basic Lookup (Employee ID → Department)

=XLOOKUP(H2, $A:$A, $C:$C, "Not found") → If H2 = 1003, the result is Finance.

2) Return Multiple Values (Fill 3 Columns at Once)

Enter the formula in H6, and it spills automatically into I6:J6 (dynamic array).

=XLOOKUP(H6, $A:$A, $B:$D, "Not found")

3) Approximate Match (Tiered Rates/Pricing Table)

After sorting the key column:

=XLOOKUP(H10, $B:$B, $C:$C, "Not found", -1)

• -1 returns an exact match or the next smaller item (ascending sort required).

4) Reverse Search (Prioritize the Last Match)

=XLOOKUP(H12, $A:$A, $E:$E, "Not found", 0, -1)

Set search_mode=-1 to search from bottom to top, making it useful for returning the last matching value.

5) Two-Way Lookup (Row and Column Intersection)

=XLOOKUP(H14, $B$5:$B$12, XLOOKUP(H15, $C$4:$G$4, $C$5:$G$12), "Not found")

6) Wildcards/Partial Matches

=XLOOKUP("*"&H16&"*", $B:$B, $D:$D, "Not found", 2)

match_mode=2 uses the * and ? wildcards.

7) Multiple Criteria (AND/OR)

AND (multiplication) pattern:

=XLOOKUP(1, (Region="East")*(Account="X*"), Price, "Not found", 0, 1)

For OR, use addition:

=XLOOKUP(1, (Region="East") + (Region="West"), Price, "Not found")

Troubleshooting

When to Choose XLOOKUP Instead of VLOOKUP + XMATCH Tip

• It can look up values to the left or right by specifying the ranges, with no leftmost-column limitation like VLOOKUP.
• Control error messages directly with if_not_found.
• Use multiple-column returns to reduce repeated formulas.
• Combine it with XMATCH for fine control over lookup position and sorting modes.

Checklist & Performance Tips

• The number of rows in the lookup and return ranges must match.
• Make sorting a habit for approximate and binary searches.
• If duplicate keys are common, use reverse search (-1) to prioritize the last match.
• Manage data as an Excel table (CTRL+T) to use automatic expansion and structured references.

Related Articles

• XLOOKUP vs VLOOKUP: Which Should You Use?
• Practical VLOOKUP with Multiple Criteria
• Summarize Multiple Criteria with SUMIFS
• Fix All Excel Errors
• Complete Guide to TEXTSPLIT

References/benchmarks: Data Science Diary (2023-04-11), Microsoft Support XLOOKUP, and Exceljet’s collection of XLOOKUP examples.