Bug 160893

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.

@Balázs

Polite ping.

XLOOKUP: Internal cell value vs display value (comment 2).

I cannot test with Excel by myself ATM.

@All,

This report is still set as UNCONFIRMED. Is this a bug, or not? We should rather know it (and be solved if it is) before LO 24.8 beta arrives.

I guess the same question – internal cell value vs display value – would be relevant for XMATCH()?

Created attachment 194238 [details]
The original in ods and its xlsx version in Excel 2021

Indeed there is a difference in results, with current nightly:

Version: 24.8.0.0.alpha1+ (X86_64) / LibreOffice Community
Build ID: ae798781ef4df7a1fdef13af0bc459bf4f6e7b4c
CPU threads: 6; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Created attachment 194239 [details]
The second example in ods and its xlsx version in Excel 2021

Looks like MATCH behaves the same way - maybe this is a separate bug?

(In reply to Gabor Kelemen (allotropia) from comment #5)

> Looks like MATCH behaves the same way - maybe this is a separate bug?

IMO, the MATCH() case should rather be a separate report (to be reviewed for regressions and other possible issues too, for instance).

Is it OK to modify the current Summary field, from the current:
"XLOOKUP problem with date as text"
to something more generic, such as:
"XLOOKUP searches/matches cell's display format/value instead of cell's internal value"
or something similar to that?

The problem is not really (just) a date as text.

(In reply to ady from comment #6)

I think no need to create new report for MATCH, since it is LOOKUP specific issue which is affect on other LOOKUP functions like VLOOKUP/HLOOKUP/MATCH etc?
In that case the Summary field, can be just:
"LOOKUP functions (XLOOKUP/VLOOKUP/HLOOKUP/MATCH/XMATCH) searches/matches cell's display format/value instead of cell's internal value", (If its related to all the LOOKUP functions).

@Balázs

Considering that the problem affects XLOOKUP() (at least), are you planning on assigning this to yourself?

If I may suggest, let's see what happens when solving this for XLOOKUP(), and then we can re-check the others (whether the patch for XLOOKUO() solves any other functions).

Maybe some relation with
tdf#144740 Criteria query (COUNTIF(), MATCH() et al) for numeric values broken considering number formats
tdf#144253 Filtering does not work with formatted calculated values

(In reply to ady from comment #8)
> Considering that the problem affects XLOOKUP() (at least), are you planning
> on assigning this to yourself?
> 
This is a general issue, and only coincidentally related to the new XLOOKUP implementation (it might be more obvious there, though). It's unlikely we get to this in the near future.