- Get link
- X
- Other Apps
This article explains in detail how to use the advanced search modes of the Excel XMATCH function so that you can build fast, flexible, and highly reliable lookup formulas in real-world workbooks.
1. XMATCH overview and why advanced search modes matter
XMATCH is a modern Excel lookup function that returns the position of a value within a range or array.
Compared with MATCH and VLOOKUP, XMATCH supports dynamic arrays, more intuitive parameters, and powerful options such as wildcard search and multiple search directions.
The real power of XMATCH appears when you start using its advanced search_mode argument.
By controlling search_mode, you can do things such as find the last occurrence instead of the first, run binary searches on sorted data for large performance gains, or search from bottom to top to pick the latest record that meets a condition.
2. XMATCH syntax recap
XMATCH has the following syntax.
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])lookup_valueis the value you want to find.lookup_arrayis the one-dimensional range or array to search.match_modecontrols how Excel treats the value (exact match, next smaller, next larger, wildcard, etc.).search_modecontrols the direction and method Excel uses to search the array.
Many users leave search_mode at its default and never touch it.
However, for advanced lookups and large datasets, choosing the right search mode is critical for correctness and performance.
3. The four XMATCH search modes explained
The search_mode argument can take one of four values.
| search_mode | Search behavior | Typical use cases |
|---|---|---|
| 1 (default) | Search from first to last (top to bottom or left to right). | Standard lookups where the first match is required. |
| -1 | Search from last to first (bottom to top or right to left). | Find the last occurrence, latest transaction, or last non-blank value. |
| 2 | Binary search in ascending order. | Very fast lookups on large ranges sorted in ascending order. |
| -2 | Binary search in descending order. | Very fast lookups on large ranges sorted in descending order. |
The choice between linear search (1, -1) and binary search (2, -2) has both logical and performance consequences.
Linear search checks values one by one, while binary search jumps through the list using the fact that it is sorted.
Note : Binary search modes 2 and -2 require the lookup_array to be sorted correctly. If the data is not sorted, XMATCH may return incorrect positions without any error.
4. Forward vs reverse search with modes 1 and -1
The most accessible advanced feature of XMATCH is its ability to search in reverse using search_mode = -1.
This makes it easy to find the last occurrence of a value, which is a common requirement in reporting, accounting, and log analysis.
4.1 Finding the first occurrence (default behavior)
Assume you have dates in column A and product codes in column B.
You want to find the first date when product "P-100" appears.
XMATCH("P-100", B2:B100, 0, 1)match_mode = 0forces an exact match.search_mode = 1searches from top to bottom.
The result is the position of the first row where "P-100" appears.
4.2 Finding the last occurrence of a value (reverse search)
Now suppose you want the last date where "P-100" appears.
Change the search mode to -1.
XMATCH("P-100", B2:B100, 0, -1)This searches from bottom to top and returns the position of the last match.
To retrieve the corresponding date, combine XMATCH with INDEX.
=INDEX(A2:A100, XMATCH("P-100", B2:B100, 0, -1))This pattern is extremely useful when you need the most recent transaction, the latest status, or the last non-blank value.
4.3 Getting the last non-blank value in a range
To find the last non-blank value in a row or column, you can use a slightly more advanced pattern that uses XMATCH with a logical test.
Assume values are in B2:M2 and some trailing cells may be blank.
=INDEX(B2:M2, XMATCH(1, --(B2:M2<>""), 0, -1))Explanation.
(B2:M2<>"")creates an array of TRUE/FALSE flags indicating non-blank cells.--converts TRUE to 1 and FALSE to 0.XMATCH(1, --(B2:M2<>""), 0, -1)searches from right to left for the first 1, which corresponds to the last non-blank cell.INDEXreturns the value at that position.
5. Binary search modes 2 and -2 for large sorted datasets
Binary search modes are where XMATCH advanced search really shines for performance.
They are especially helpful when dealing with large dynamic arrays or tables containing tens of thousands of rows.
5.1 When to use binary search
Use search_mode = 2 when.
- Your lookup_array is sorted in ascending order.
- You frequently perform approximate lookups (such as tax brackets or discount tiers).
- Performance is important because the range is large.
Use search_mode = -2 when.
- Your lookup_array is sorted in descending order.
- You want similar behavior but on data sorted from largest to smallest.
Note : Binary search modes do not change the matching rules. You still control exact versus approximate behavior with the match_mode argument. The search_mode only defines direction and method.
5.2 Example: Tax bracket lookup with ascending binary search
Assume the following tax brackets are stored in A2:B6.
| Income threshold | Tax rate |
|---|---|
| 0 | 5% |
| 20000 | 10% |
| 40000 | 15% |
| 60000 | 20% |
| 80000 | 25% |
Income in cell D2.
=INDEX(B2:B6, XMATCH(D2, A2:A6, 1, 2)) Explanation.
match_mode = 1means exact match or next larger value.- Because thresholds are sorted ascending, XMATCH with
search_mode = 2performs a binary search. - Performance is significantly better than a linear scan when the table is large.
Note : When using match_mode 1 or -1 with search_mode 2 or -2, always verify that the lookup_array is strictly sorted as required. Duplicates or unsorted segments can produce unexpected results.
5.3 Example: Discount lookup with descending binary search
Assume you store discounts in descending order of quantity in A2:B6.
| Minimum quantity | Discount |
|---|---|
| 1000 | 20% |
| 500 | 15% |
| 200 | 10% |
| 100 | 5% |
| 0 | 0% |
Quantity in D2.
=INDEX(B2:B6, XMATCH(D2, A2:A6, -1, -2)) Here.
match_mode = -1means exact match or next smaller item.search_mode = -2uses binary search, assuming the thresholds are sorted in descending order.- This combination is ideal for tiered pricing or discount logic.
6. Combining match_mode and search_mode for robust logic
The real flexibility of XMATCH advanced search modes appears when you combine different match_mode and search_mode values to implement specific business rules.
| Goal | match_mode | search_mode | Explanation |
|---|---|---|---|
| First exact match | 0 | 1 | Standard lookup from top to bottom. |
| Last exact match | 0 | -1 | Reverse lookup, starting from the bottom. |
| Nearest lower value in ascending list | -1 | 2 | Binary search on an ascending sorted table. |
| Nearest higher value in descending list | 1 | -2 | Binary search on a descending sorted table. |
| Wildcard search, first match | 2 | 1 | Linear search with wildcards in unsorted data. |
| Wildcard search, last match | 2 | -1 | Reverse wildcard search to capture the final match. |
7. Practical XMATCH advanced search templates
The following patterns can be reused across many workbooks.
7.1 Last transaction date for a customer
Data.
- Customer names in B2:B1000.
- Transaction dates in A2:A1000.
- Target customer name in E2.
=INDEX(A2:A1000, XMATCH(E2, B2:B1000, 0, -1)) This returns the last transaction date for that customer using reverse search.
7.2 Latest status for an ID
Data.
- ID in B2:B5000.
- Status in C2:C5000.
- IDs may repeat as the status changes over time.
=INDEX(C2:C5000, XMATCH(G2, B2:B5000, 0, -1)) Here, G2 contains the ID you want to check.
Because search_mode is -1, XMATCH finds the last occurrence and INDEX returns the latest status.
7.3 Dynamic reporting period lookup with binary search
Assume you have a table of reporting periods defined by end dates in ascending order.
- End dates in A2:A24.
- Period labels (for example "2025-Q1") in B2:B24.
- Transaction date in D2.
=INDEX(B2:B24, XMATCH(D2, A2:A24, 1, 2)) This finds the first period end date that is greater than or equal to the transaction date, using a binary search on sorted dates.
7.4 Wildcard search for flexible text matching
Wildcards are controlled by match_mode = 2.
search_mode can still be used to decide whether you want the first or last match.
=XMATCH("*XL*", B2:B100, 2, -- wildcard match -1) -- search from bottom to top This example returns the position of the last cell in B2:B100 that contains "XL" anywhere in the text.
8. Debugging XMATCH advanced search results
Because XMATCH advanced search modes interact with match_mode and data sorting, it is important to systematically debug unexpected results.
8.1 Handling #N/A errors
#N/A means that XMATCH did not find a matching position based on the rules you specified.
Key checks.
- Confirm that lookup_value exists in lookup_array for exact matches.
- For approximate and binary searches, confirm that lookup_array is properly sorted.
- Check for extra spaces, hidden characters, or inconsistent data types (number stored as text, etc.).
A robust pattern for user-facing reports is to wrap XMATCH inside IFERROR.
=IFERROR( INDEX(ReturnRange, XMATCH(...)), "Not found" )8.2 Verifying sorting for binary search modes
When using search_mode 2 or -2, sorting is essential.
If the data is not sorted strictly in ascending or descending order, binary search may return the wrong position without throwing an error.
Note : A good practice is to store the lookup table in an Excel Table object and enforce sorting on the key column. You can apply a sort once and then design your data entry process so that new rows are always appended in the correct order.
8.3 Checking direction and duplicates
For data with duplicates, the direction of search_mode determines which occurrence you get.
search_mode = 1returns the first match.search_mode = -1returns the last match.
Be explicit about which one you need in your business logic.
9. Performance considerations for XMATCH advanced search
On small ranges, the choice between linear and binary search is largely invisible.
On large dynamic arrays, tables, or spilled ranges, the benefits of binary search become more visible.
Guidelines.
- Use linear search (1, -1) for unsorted or lightly sized ranges, especially when you need wildcard or complex conditions.
- Use binary search (2, -2) for large tables that are strictly sorted and where you frequently perform approximate lookups.
- Combine XMATCH with LET to calculate intermediate arrays once and reuse them if you have multiple lookups against the same data.
By aligning your search_mode choice with the structure of your data, you can significantly improve both accuracy and speed in your Excel XMATCH formulas.
FAQ
What is the difference between match_mode and search_mode in XMATCH?
match_mode controls how Excel compares the lookup_value with values in the lookup_array. It decides whether you require an exact match, allow approximate matches, or use wildcards.
search_mode controls the direction and algorithm used to search the array. It decides whether you search from top to bottom, bottom to top, or use binary search on sorted data.
Both arguments work together, so you should always set them intentionally based on your data and business rule.
When should I use search_mode = -1 instead of 1?
Use search_mode = -1 when you need the last occurrence of a value instead of the first. Typical scenarios include getting the latest transaction, the most recent status, or the last non-blank value in a row or column.
If you want to emulate a "latest record" lookup, search_mode = -1 is usually the correct choice.
Do I always need to sort my data for XMATCH advanced search?
No. Sorting is only required when you use binary search modes 2 or -2, or when you rely on approximate match behavior with match_mode 1 or -1.
If you use exact match with linear search (search_mode 1 or -1), the data does not need to be sorted. However, sorting can still help with readability and maintenance.
Can XMATCH search to the left like an improved VLOOKUP?
Yes. XMATCH returns a position rather than a value, so you can combine it with INDEX to retrieve values from any column, regardless of position.
XMATCH itself works on one-dimensional arrays, but because it returns an index, you can use that index against any corresponding range, including those to the left of the lookup column. This is one of the main advantages of XMATCH over VLOOKUP.
Does using XMATCH with binary search make a visible speed difference?
On small tables, the difference is minor. On large tables with many thousands of rows and many repeated lookups, binary search can significantly reduce calculation time.
The main requirement is that your lookup_array must be sorted in the correct order for binary search to work reliably.