XMATCH fails on numeric lookup with search_mode 'last to first' and search_mode 'Exact match or *' with an exact match

Aspose.Cells Product Family
1

Attached is a test spreadsheet that shows the issue.

XMATCH fails when doing a numeric lookup when the lookup value is an exact match when the XMATCH match_mode is either ‘Exact match or next largest item’ or ‘Exact match or next smallest item’ AND the search_mode is search_mode ‘last to first’.

I’ve seen the issue with all versions that I tried: 24.6 and 24.10.

Note that there is a workaround by updating the XMATCH to use ‘last to first’ since ‘first to last’ vs ‘last to first’ doesn’t matter in this case in terms of results, but we are not the authors of the spreadsheet where we are seeing this issue.

val worksheet = workbook.worksheets["Sheet1"]
listOf(
    "XMATCH with match_mode 'Exact match or next largest item' and search_mode 'last to first' fails, should return 6",
    worksheet.calculateFormula("=XMATCH(34500, E13:E18, 1,-1)"),

    "XMATCH with match_mode 'Exact match or next largest item' and search_mode 'last to first' and non-exact input succeeds, returns 6",
    worksheet.calculateFormula("=XMATCH(34499, E13:E18, 1,-1)"),

    "XMATCH with match_mode 'Exact match or next largest item' and search_mode 'first to last' succeeds, returns 6",
    worksheet.calculateFormula("=XMATCH(34500, E13:E18, 1,1)"),

    "XMATCH with match_mode 'Exact match or next smallest item' and search_mode 'last to first' fails, should return 6",
    worksheet.calculateFormula("=XMATCH(34500, E13:E18, -1,-1)"),

    "XMATCH with match_mode 'Exact match or next smallest item' and search_mode 'first to last' succeeds, returns 6",
    worksheet.calculateFormula("=XMATCH(34500, E13:E18, -1,1)"),

    "XMATCH with match_mode 'Exact match' and search_mode 'last to first' succeeds, returns 6",
    worksheet.calculateFormula("=XMATCH(34500, E13:E18, 0,-1)"),

    "XMATCH with match_mode 'Exact match' and search_mode 'first to last' succeeds, returns 6",
    worksheet.calculateFormula("=XMATCH(34500, E13:E18, 0,1)"),

    ).forEach { println(it) }

The above produces the output:

XMATCH with match_mode 'Exact match or next largest item' and search_mode 'last to first' fails, should return 6
5.0
XMATCH with match_mode 'Exact match or next largest item' and search_mode 'last to first' and non-exact input succeeds, returns 6
6.0
XMATCH with match_mode 'Exact match or next largest item' and search_mode 'first to last' succeeds, returns 6
6.0
XMATCH with match_mode 'Exact match or next smallest item' and search_mode 'last to first' fails, should return 6
5.0
XMATCH with match_mode 'Exact match or next smallest item' and search_mode 'first to last' succeeds, returns 6
6.0
XMATCH with match_mode 'Exact match' and search_mode 'last to first' succeeds, returns 6
6.0
XMATCH with match_mode 'Exact match' and search_mode 'first to last' succeeds, returns 6
6.0

ExampleWorkbook.xlsx.zip (8.0 KB)

2

@twadzins
By testing with sample code and files on the latest version v24.10, we can reproduce the issue. Found that XMATCH fails on numeric lookup with some search_mode and match_mode settings.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-46146

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

3

@twadzins

We are pleased to inform you that your issue (Ticket ID: “CELLSJAVA-46146”) has been resolved. The fix/enhancement will be included in the next (upcoming) release (Aspose.Cells v24.11 package) that we plan to release in the first half of November 2024. You will be notified when new version is published.

1 Like