Created attachment 192274 [details] Example file from Excel 2021/O365 Attached example file was taken from https://trumpexcel.com/xlookup-function/ and demonstrates various uses of the XLOOKUP function. After the patch 1. Open attached file 2. Go to the "Ex 5" sheet 3. In the A12 cell enter a value from the A2:A8 range such as "Ned" -> In B12 the XLOOKUP gives back #N/A error instead of the matching value from the B2:B8 range. This happens because XLOOKUP has the ability to run an embedded function as its 4th if_not_found parameter, and here that one gives back the #N/A error. Excel handles this in an unusual way and does not propagate this error as the final result of the XLOOKUP function, but Calc does. Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 9de73be119eb288ee0a1f54e9c0f2b236ef41821 CPU threads: 2; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: hu-HU (hu_HU); UI: en-US Calc: threaded (this is local build from https://gerrit.libreoffice.org/c/core/+/131905 )
Created attachment 192275 [details] The example file after opening, this works
Created attachment 192276 [details] The example file after modifying the search input of XLOOKUP
FWIW, (in an attempt to help make sense for other readers, *I hope*) (In reply to Gabor Kelemen (allotropia) from comment #0) > After the patch I guess you mean tdf#127293 comment 39, which was posted to this Bugzilla tracker after your own tdf#127293 comment 38, just a few hours ago (not even a day) ATM.
XLOOKUP still failing after (second) patch from tdf#127293 comment 44, dated 2024-02-09, using Calc dated 2024-02-11 Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 17fc445938dedb05125a6d6a5b4ce7f34ea95f59 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: en-US (es_AR); UI: en-US Calc: threaded I have also tested with 2 other spreadsheet tools and using STR from comment 0 works correctly there, but generates #N/A (not available error) in LO Calc. In Calc, attachment 192274 [details] > worksheet "Ex5" > when the search string (cell A12) is found in the list in range A2:A8, for the chained XLOOKUP to provide the "correct" result (although, only by chance), the same string has to be also part of the other list in range F2:F8 too. If the search string is not part of the second range F2:F8 too, then the result is #N/A. Or, IOW, the formula appears to be able to retrieve _the result_ from both lists (B2:B8; G2:G8), but it is searching the criterion only on the second range F2:F8. Translated to the array formula: =XLOOKUP(A12;A2:A8;B2:B8;XLOOKUP(A12;F2:F8;G2:G8)) ... it is only executing the search of the criterion on the second XLOOKUP(), which is the "Result_if_not_found" of the first XLOOKUP(), but it can retrieve the result from the first XLOOKUP() (too).
(In reply to ady from comment #4) > Translated to the array formula: > =XLOOKUP(A12;A2:A8;B2:B8;XLOOKUP(A12;F2:F8;G2:G8)) > > ... it is only executing the search of the criterion on the second > XLOOKUP(), which is the "Result_if_not_found" of the first XLOOKUP(), but it > can retrieve the result from the first XLOOKUP() (too). Additional info that might point to the cause of this failure... A. Change the above _array_ formula (worksheet "Ex5", cell B12) from: =XLOOKUP(A12;A2:A8;B2:B8;XLOOKUP(A12;F2:F8;G2:G8)) to: =XLOOKUP(A12;A2:A8;B2:B8;XLOOKUP(A12;F2:F8;G2:G8;0)) (note the additional zero; i.e. since the first XLOOKUP() has a "Result_if_not_found", I am adding the equivalent argument to the second XLOOKUP()) * The result when cell A12 is "Ned" (without quotation marks) is now correct (67). B. Same as "A.", but instead of using the fixed numeric value of zero as the second "Result_if_not_found", I used a "SUM(0)" function. * The result when cell A12 is "Ned" (without quotation marks) is still correct, same as in case "A.". C. Same as "A.", but instead of using the fixed numeric value of zero as the second "Result_if_not_found", I used a "NA()" function. * The result when cell A12 is "Ned" (without quotation marks) is back to generate an incorrect result, #N/A. Conclusion: The presence of a second "Result_if_not_found" _might_ trigger the correct result, depending on the specific type of value and/or function used for it. The result should not change, whether using (or not) the second "Result_if_not_found", and whichever value or function is used on that argument. Perhaps errors are being carried over in the chain of functions in a different manner than it was supposed to happen?
Thanks for the explanation and investigation Ady. :) =XLOOKUP(A12;A2:A8;B2:B8;XLOOKUP(A12;F2:F8;G2:G8;0)) The reason why it is work and the original ones not, because in the above function the inner function give back not an error msg (like #N/A) but a 0 value if the searched value not found. In that case the evaluation of the full formula will not stop after the evaluation of the inner function. --> After evaluation of the embedded XLOOKUP this is the result: =XLOOKUP(A12;A2:A8;B2:B8;0) In case of the original case the result was: =XLOOKUP(A12;A2:A8;B2:B8;#N/A) -> #N/A which is an error type. In Calc if an inner, embedded function gives back an error (like #N/A) the evaluation of the full formula will stop after the inner function. This is the basic work of the functions in Calc and also in Excel. But the XLOOKUP is an exception in excel. :)
(In reply to Balázs Varga (allotropia) from comment #6) > In Calc if an inner, embedded function gives back an error (like #N/A) the > evaluation of the full formula will stop after the inner function. This is > the basic work of the functions in Calc and also in Excel. But the XLOOKUP > is an exception in excel. :) Thank you for the explanation. Then this is not just the case of (a second) chained XLOOKUP, but any such inner error case would trigger the unexpected result (in comparison to what other tools do). Considering that compatibility in Calc in general, and for this function in particular, is very important, then Calc should have the same way of dealing with XLOOKUP() as Excel regarding errors. @X1sc0, this should also be important for regression tests (at the appropriate time, whenever they will be introduced). FWIW, as I mentioned in comment 4, I have tested 2 other spreadsheet tools that provide the same result as Excel.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/c7664873f8aff4e1fa4f857e87821a49f858a73b tdf#159467 XLOOKUP propagate error only for active paths 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.
(In reply to Commit Notification from comment #8) > Affected users are encouraged to test the fix and report feedback. Text below from https://github.com/goosepirate/lox365/issues/19#issuecomment-1997309385 Anyway, it sort of works but not completely. If I do: XLOOKUP( search1, lookup1, XLOOKUP( search2, lookup2, return2 ) ) and the search criteria can be found then it works. If I do: XLOOKUP( search1, lookup1, XLOOKUP( search2, lookup2, return2 ), "not found" ) and the search criteria cannot be found then rather then "not found" I get Err: 504 It therefore follows that the problem I initially reported i.e. XLOOKUP( search1, lookup1, return1, XLOOKUP( search1, lookup2, return2, "not found" ) ) Also reports Err: 504 (Use of Search1 twice is intentional. The original problem was to find a value in one of two tables.)
(In reply to Óvári from comment #9) > (In reply to Commit Notification from comment #8) @Óvári, Have you replicated that behavior on a recent 24.8 alpha? **I might be misunderstanding**, but that comment you quoted (in your tdf#159467 comment 9) *seems* the same as I reported in tdf#159467 comment 5. The steps I followed in order to replicate what @Snudz (seemingly) reported on github (at least, the way I understand it) are (similar to my tdf#159467 comment 5): 1. Open attachment 192274 [details], worksheet "Ex 5", cell B12. 2. Edit B12 in the following way: From the original in attachment 192274 [details]: =XLOOKUP(A12;A2:A8;B2:B8;XLOOKUP(A12;F2:F8;G2:G8)) (as array formula) ...change it to (also as array formula!): =XLOOKUP(A12;A2:A8;B2:B8;XLOOKUP(A12;F2:F8;G2:G8;"not found")) where (IIUC): "search1" (and "search2"): A12 "lookup1": A2:A8 ("return1": B2:B8) "lookup2": F2:F8 "return2": G2:G8 3. Edit cell A12, from "Amy" to "x" (i.e. a value that is not listed in either A2:A8 nor in F2:F8). With a recent 24.8 alpha, I get "not found" (as expected). So I have at least 2 points here: 1A_ My test seems to have a different result than @Snudz's test (IF I understood the test correctly). 2B_ In your quoted text, there are these: 2B1. XLOOKUP( search1, lookup1, XLOOKUP( search2, lookup2, return2 ) ) 2B2. XLOOKUP( search1, lookup1, XLOOKUP( search2, lookup2, return2 ), "not found" ) 2B3. XLOOKUP( search1, lookup1, return1, XLOOKUP( search1, lookup2, return2, "not found" ) ) While I used the above "2B3" for this test, where is "return1" in the above 2 points "2B1" and "2B2"? Is that a typo (maybe from @Snudz)? Or should the argument be left blank empty? Or something else that I don't understand? So, I repeat the question(s): @Óvári, have you attempted to replicate what @Snudz reported, in a recent 24.8 alpha? Could anyone please try (ignoring the steps I followed/described here)? Could the question about "return1" be clarified, please?
Apologies. The example I gave when testing was a quick example I grabbed of the web to test it. This was doing a 2D lookup which is not what I was actually trying to do in my real set-up. So my testing was flawed. Have had more time to look at it and the example I gave doesn't work for me in Excel either. So, I grabbed the actual excel spreadsheet I was transferring to LibreCalc and used that directly and the nested XLOOKUP as being used in that spreadsheet does now work in LibreCalc. The actual example I should have used was a generic scoring system. Assume a team is the home team and find their score, if not found then they must be the away team, so search the away teams and get their score. So: XLOOKUP( TeamName, HomeTeams, HomeScores, XLOOKUP( TeamName, AwayTeams, AwayScores, "Error not found") ) Apologies again for wasting your time and thank you for fixing the original problem.
@Stephen, Thank you for the feedback. FWIW and JIC, lox365 [1] is an extension, which adds several Excel 365 functions to LO Calc, but it is not exactly the same as the XLOOKUP() function that is being added to LO 24.8 (alpha ATM) from tdf#127293 + tdf#159467. Hopefully, the new addition (from these 2 tdf's) has even better compatibility with Excel 2021 and Excel 365. The tests I performed (mentioned in prior comments) used the newly added XLOOKUP(), without using the lox365 extension. [1] <https://github.com/goosepirate/lox365>