Created attachment 193773 [details] Test for XMATCH and empty cell Open attached document. It has cells with expected value (what Excel calculates) and actual value (what LibreOffice calculates) and a cell, which shows the formula as text. Press Ctrl+Shift+F9 to force recalculation. The similar problem for XLOOKUP is in 160711 and Balázs has fixed that already.
Reproducible Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 7c2ed9919d6d9d286d9062b91577d6bb2b7de8aa CPU threads: 16; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded On the first case, need to have at least one larger than searched value. On the second case, having an empty in double quotes (=XMATCH("";B2:B15)) as the first parameter also doesn't work.
Although I cannot check with Excel by myself ATM, a formula such as: =XMATCH("";B2:B15) using an "empty string" as lookup argument is not parsed by Excel as searching for blank empty cells. IIRC, Excel's XMATCH() searches for blank empty cells when the first lookup argument is omitted, just as with XLOOKUP(). This is documented for XLOOKUP but not for XMATCH. This indeed needs to be corrected for Calc's XMATCH() as of 2024-05-13. At any rate, please review what Excel does with the "empty string" argument, as I cannot do it myself ATM.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/204c5e14188022f34afb05776eb20669b01ea6d4 tdf#160753 - sc: fix XMATCH function in search for empty cell It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.