How to Find the Last Nonblank Entry with XMATCH Binary Search in WPS Office Spreadsheets
Question details
The user needs to use XMATCH in WPS Spreadsheets to find the last text or nonblank entry in a column and correctly apply binary search mode for sorted lookup arrays.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a column of text data in a workbook and needing to locate the last text entry, or accelerate the lookup by using XMATCH binary search on sorted data.
- Observed behavior
- XMATCH returns the position of the last text entry when match mode 2 and search mode -1 are used, and performs binary search when search mode -2 is applied to a correctly sorted array.
Check your data range and sort order before choosing the search mode: use search mode -1 for a normal last-to-first scan, and use search mode -2 for binary search only when the lookup array is already sorted according to the selected match mode.
Use XMATCH with a Wildcard to Find the Last Text Entry in a Column
Use this formula when you want the position of the last text value in a range without sorting the data first.
XMATCH supports wildcard matching when the match mode is set to 2. Combined with search mode -1, it scans the lookup array from last to first, so the formula returns the last text entry in one step.
Open your workbook in WPS Spreadsheets and click the cell where you want the XMATCH result to appear.
Type =XMATCH("*",A:A,2,-1) and press Enter. If your data is in a different column, replace A:A with that range.
Check that the cell displays the relative position of the last text entry in the selected column.
Use XMATCH Search Mode -2 for Binary Search on Sorted Data
Use this faster lookup method only when your lookup array is already sorted as required by the chosen match mode.
Use XMATCH Lookups in WPS Spreadsheets Without Barriers
WPS Spreadsheets supports the modern XMATCH function, so you can find the last text entry, apply binary search on sorted data, and work with Excel-compatible workbooks for free.
- 1. Download and install WPS Office: Go to the official WPS Office download page and install the latest version for your device.
- 2. Open your workbook: Launch WPS Spreadsheets and open the .xlsx or .xls workbook that contains the data you want to search.
- 3. Apply the XMATCH formula: Click the target cell and enter the appropriate formula, such as =XMATCH("*",A:A,2,-1) for the last text entry.
- 4. Save your file: Save the workbook in Excel-compatible .xlsx format to preserve the formulas and results.
Frequently Asked Questions
What is the correct XMATCH formula to find the last text entry in WPS Spreadsheets?
Use =XMATCH("*",A:A,2,-1). Match mode 2 enables wildcard matching, and search mode -1 scans from last to first. Replace A:A with your target column.
When should I use XMATCH binary search in WPS Spreadsheets?
Use search mode -2 only when the lookup array is already sorted as required by your match mode. For example, exact match mode 1 requires an ascending sort order.
Why does my XMATCH binary search return the wrong result?
Binary search assumes a sorted lookup array. If the data is unsorted or sorted in the wrong order for the selected match mode, XMATCH may return an incorrect position. Sort the data first or use a linear search mode.
Does WPS Spreadsheets support wildcard matching with XMATCH?
Yes. WPS Spreadsheets supports XMATCH with match mode 2 for wildcard matching, which can be combined with search mode -1 to find the last matching text entry.
