Why MATCH and XMATCH Return Different Approximate Matches in WPS Spreadsheets
Question details
The user needs to understand why the MATCH and XMATCH functions return different approximate match positions in WPS Spreadsheets and how to make XMATCH replicate the MATCH binary search result.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is comparing approximate lookup results between MATCH and XMATCH on a sorted array or range.
- Observed behavior
- MATCH can use a binary search and return the last qualifying value, while XMATCH performs a linear search from first to last by default, producing a different position.
Confirm your lookup array is sorted in the order required by the match mode and search mode you plan to use, and verify that your WPS Spreadsheets version includes XMATCH.
Use XMATCH Search Mode -2 to Match MATCH Binary Search in WPS Spreadsheets
Use this solution when you need XMATCH to return the same approximate match position as MATCH on a sorted lookup array.
By default, XMATCH searches linearly from first to last, while MATCH may use a binary search on sorted data and return the last qualifying item. Passing -2 as the fourth argument makes XMATCH search from the end using binary search.
Launch WPS Office, open WPS Spreadsheets, and load the worksheet that contains the MATCH and XMATCH formulas.
Click the cell where you want the aligned XMATCH result to appear.
Type =XMATCH(0,{3,2,2},1,-2) or reference your sorted range, such as =XMATCH(0,A1:A10,1,-2), and press Enter.
Compare the result to your MATCH formula to confirm that XMATCH now uses the equivalent binary search behavior.
Explicitly Set Match Mode and Search Mode for Sorted Lookup Arrays
Use this when you want to prevent unexpected approximate match differences by choosing the correct mode arguments for your data.
Align XMATCH with MATCH in WPS Spreadsheets
Use WPS Spreadsheets to enter an XMATCH formula with search mode -2 so the approximate match result matches MATCH binary search behavior. WPS Office is free, lightweight, and compatible with Excel files.
- 1. Open WPS Spreadsheets: Launch WPS Office and open a blank workbook or the Excel file you need to edit.
- 2. Prepare the sorted lookup range: Enter your lookup array in a column, for example A1:A10, and ensure it is sorted according to the search mode you will use.
- 3. Enter the XMATCH formula: Click the result cell, type =XMATCH(0,A1:A10,1,-2), and press Enter.
- 4. Compare with MATCH: Verify that the XMATCH position matches your MATCH approximate lookup result in WPS Spreadsheets.
- 5. Save your workbook: Press Ctrl+S or go to Menu > Save to keep the updated formulas in your workbook.
Frequently Asked Questions
Why do MATCH and XMATCH return different approximate match positions?
MATCH can use a binary search on sorted data, while XMATCH defaults to a linear search from first to last. You can make them match by setting XMATCH search mode to -2 or 2 depending on the sort order.
What is the exact XMATCH formula to match the MATCH example?
Use =XMATCH(0,{3,2,2},1,-2) in WPS Spreadsheets. The fourth argument -2 tells XMATCH to search from the end using binary search.
Does XMATCH search mode -2 require the lookup array to be sorted?
Yes. Binary search modes require the lookup array to be sorted in the expected order. For search mode -2, the array should be sorted descending; for search mode 2, it should be sorted ascending.
Can I use MATCH and XMATCH in WPS Spreadsheets?
Yes. WPS Spreadsheets supports MATCH and XMATCH with Excel-compatible syntax, so you can open and edit workbooks that use these functions.
What match modes are available in XMATCH?
XMATCH supports 0 for exact match, -1 for exact match or next smaller, 1 for exact match or next larger, and 2 for wildcard match.
