Created attachment 193922 [details] Lookup_array with dates Open attached file. Save it as .xlsx and open the saved file in Excel. Compare. Problem: (1) If the Lookup_value is a text that looks like a date, it is interpreted as number. (2) If the Lookup_array contains dates, neither a lookup_value in serial date form nor as result of the DATE function matches the dates.
Created attachment 193924 [details] Sample file with XLOOKUP and MATCH It seems to have the same differences as MATCH(). Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: f4ef5435df5560e6b6b061ce4053c71e2819bf51 CPU threads: 16; OS: Windows 10.0 Build 22631; UI render: Skia/Vulkan; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded Excel $71,29 $86,92 =XLOOKUP("2024-01-15";A2:A4;B2:B4;"no invoice";0;2) 33,12 no invoice =XLOOKUP(DATE(2024;1;15);A2:A4;B2:B4;"no invoice";0;2) $71,29 $86,92 =XLOOKUP(T("2024-01-15");A2:A4;B2:B4;"no invoice";0;2) 33,12 no invoice =XLOOKUP(45306;A2:A4;B2:B4;"no invoice";0;2) $3,00 $2,00 =MATCH("2024-01-15";A2:A4;0) 1 #N/A =MATCH(DATE(2024;1;15);A2:A4;0) $3,00 $2,00 =MATCH(T("2024-01-15");A2:A4;0) 1 #N/A =MATCH(45306;A2:A4;0)
(In reply to Regina Henschel from comment #0) > Problem: > (1) If the Lookup_value is a text that looks like a date, it is interpreted > as number. > (2) If the Lookup_array contains dates, neither a lookup_value in serial > date form nor as result of the DATE function matches the dates. While I don't have access to Excel 2021 to test its results ATM, I think that the description in comment 0 given to the results from Calc is slightly inaccurate. I think the problem (assuming that Excel 2021 provides the results as in attachment 193922 [details] cell range B7:B10, which I cannot review by myself ATM) is that Calc is comparing/matching according to the ** display format **, rather than according to cell value. STR: 1. Open attachment 193922 [details]. 2. Note the resulting value of cell C7: 33.12. 3. On cell A2, change (display) format from original YYYY-MM-DD to YY-MM-DD. 4. Recalculate Hard. 5. Note that the result on cell C7 changed from 33.12 to 86.92. Explanation (my guess): In cell C7: =XLOOKUP("2024-01-15";A2:A4;B2:B4;"no invoice";0;2) is looking for "2024-01-15" and originally found the first match in cell A2. Once cell A2 changed its (display) format, XLOOKUP no longer finds "2024-01-15" in cell A2, despite cell A2 containing the same internal value (serial date/time "45306"). The new first match is located in cell A3, still displaying the same value in the same display format that matches "2024-01-15". Conclusion (my guess): Calc's XLOOKUP is matching the values according to display format, not by internal value. Assuming the values in cell range B7:B10 are to be taken as correct – I just don't have any way to confirm it ATM by myself – then Excel 2021 would seem to perform the comparison/match according to internal value, not by "display format". Please don't just trust this and test by yourself, because I might very well be wrong and I cannot test this by myself ATM. (In reply to m_a_riosv from comment #1) > Created attachment 193924 [details] > Sample file with XLOOKUP and MATCH > $3,00 $2,00 =MATCH("2024-01-15";A2:A4;0) > 1 #N/A =MATCH(DATE(2024;1;15);A2:A4;0) > $3,00 $2,00 =MATCH(T("2024-01-15");A2:A4;0) > 1 #N/A =MATCH(45306;A2:A4;0) I get a different result than that. For instance: =MATCH("2024-01-15";A2:A4;0) results in "1" (not "2") in Calc (after Recalculate Hard). Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: bc7501d30ab9a3a5edec66d2f0022177f85d6127 CPU threads: 4; OS: Windows 10 (10.0 build 19045); UI render: Skia/Raster; VCL: win Locale: en-US (es_AR); UI: en-US Calc: CL threaded At any rate, we should be careful when comparing XLOOKUP/XMATCH with the older VLOOKUP/MATCH functions, considering the explicit argument for "binary search" available in the newer "X" functions. We also don't always know whether the "sorting" used in Calc's functions (for the binary search) is _exactly_ the same sorting order used in Excel.