MicrosoftJoeMcDaid Wonderful stuff, thank you!
It seems the parameter interpretation for match_mode in XMATCH are inverted from those for MATCH. So a match_mode of -1 in XMATCH means exact or next smaller, while the same match_type of -1 for MATCH means exact or next larger.
Take the XMATCH help Example 2: The formula shown as =XMATCH(F2, C3:C9, 1) will give the same result as =MATCH(F2, C3:C9, -1).
Similarly in XMATCH help Example 4, the formula =XMATCH(4.5,{5,4,3,2,1},1) returns the same as =MATCH(4.5,{5,4,3,2,1},-1).
Why not make this parameter interpretation consistent?
This might be an intentional change, since XMATCH is a different function and doesn't require the array to be sorted etc. But I found the wording in the help saying "next smallest item" confusing, especially when comparing with the much better MATCH help which says "largest value that is less than or equal". I think this change should be called out somewhere. Otherwise it is tempting to just replace MATCH with XMATCH since it is presumably safer and better (by not requiring the sorted inputs).
There are also some minor bugs in the help documentation:
* XMATCH Example 1 - has two mistakes: The search_mode should be 2 for wildcard search, and even then the string "Gra?" is not enough to match "Grape".
* XLOOKUP Example 4 - the numbers in the table and result don't add up, neither for the Qty * Price = Total columns, nor the XLOOKUP result shown (could be rounding in the lookup result, but looks confusing).
