https://bugs.documentfoundation.org/show_bug.cgi?id=159467
--- Comment #5 from ady <[email protected]> --- (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? -- You are receiving this mail because: You are the assignee for the bug.
