XMATCH in WPS Spreadsheet: Find an Exact Relative Position

The official WPS XMATCH lesson documents the behavior used here. It identifies WPS Office for Windows Personal. Reviewed October 19, 2026, this Chinese-language evidence does not establish an exact introduction build, every current edition, or English-global, Mac, Linux, web or mobile behavior. No new WPS execution or native screenshot was possible; the examples are independent controls or explicitly conditional fixtures.

=XMATCH(lookup_value,lookup_array,match_mode,search_mode)

The source lists all four parameters. The examples supply them explicitly:

• lookup_value: item to find, here a text product code.
• lookup_array: the single-column list to search.
• match_mode: 0 requests an exact match, as explained in the lesson.
• search_mode: 1 searches first to last. This mapping was visually checked in the official search-mode table. The same table documents -1 for reverse search.

Other matching and binary-search modes are not required for this guide. Their presence in the interface is not permission to use them without understanding their ordering requirements.

Enter this complete source:

Enter =XMATCH("K-20",A2:A5,0,1) in D2. Independently, K-20 is the third member of the lookup list, so the expected scalar result is 3. Its worksheet cell is A4; a returned 4 would confuse the worksheet row with the relative position.

This exact lookup does not require sorting this fixture. The deliberately unsorted product codes emphasize that search mode 1 is a forward scan rather than a binary-search request.

In D3 use:

=INDEX(B2:B5,XMATCH("K-20",A2:A5,0,1))

XMATCH supplies 3; INDEX selects the third stock value, independently expected to be 15. Both ranges start at row 2 and end at row 5. A shifted B3:B6 return range would associate the position with the wrong record.

For a missing value, =XMATCH("K-99",A2:A5,0,1) should return the lesson’s documented exact-match failure #N/A. Confirm that the code truly is absent before hiding the result: a typo and a valid missing item both deserve attention in a stock report.

On a separate sheet enter K-10, K-20, K-10, K-30 in A2:A5. With =XMATCH("K-10",A2:A5,0,1), the independently expected position is 1. With search mode -1, =XMATCH("K-10",A2:A5,0,-1) should give 3, the last occurrence’s relative position. These are conditional expectations from the documented search directions, not native execution observations.

This is useful when an ordered log contains repeated identifiers. “Last” means last in the supplied range; it means latest chronologically only if the log is actually ordered that way.

The lesson’s bulk-lookup example uses a range of lookup values and Ctrl+Shift+Enter. The examples here request one lookup value and one scalar result, so they do not need a multi-cell result rectangle. Do not apply a bulk-array requirement blindly to a scalar formula.

• Keep codes consistently stored as text for this fixture; number/text coercion and wildcard escaping are outside its scope.
• Check the range’s starting row when a position looks off by one.
• Use INDEX for the related value rather than treating the returned index as the value itself.
• Use MATCH as a separate function with its own documented contract; its modes are not interchangeable by assumption.
• See SORTBY when you want the whole list reordered instead of one position.

• WPS XMATCH lesson

Expected outputs are derived from the explicit fixtures and documented operation. They are not results observed in an installed WPS build. Check the intended edition before relying on these formulas in a shared workbook.