Nested XLOOKUP doesn't appear to work

[Snudz]

Using a 2nd XLOOKUP as the [if not found] argument does not appear to work.

XLOOKUP( value, lookup1, return1, XLOOKUP( value, lookup2, return2, "not found" ) )

[ovari]

https://bugs.documentfoundation.org/show_bug.cgi?id=127293#c44

LibreOffice 24.8 will have XLOOKUP in the core application.

Can you please test LibreOffice 24.8 to see if this issue is resolved?

Thank you

[ovari]

LibreOffice has implemented nesting XLOOKUP functions https://bugs.documentfoundation.org/show_bug.cgi?id=159467

LibreOffice Help also includes the XLOOKUP function https://help.libreoffice.org/master/en-US/text/scalc/01/func_xlookup.html

Can you please test and report any bugs? https://bugs.documentfoundation.org/show_bug.cgi?id=127293

Thank you

[Snudz]

Sorry, been busy/away. What a palava trying to find 24.8. I don't normally use libreoffice. Only doing this as a friend asked for a librecalc version of an excel spreadsheet I'd written.

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.)

[ovari]

Thank you for testing and commenting.

Your comment has been added at https://bugs.documentfoundation.org/show_bug.cgi?id=159467#c9 for the LibreOffice developers

[Snudz]

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 have above 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.

[ovari]

@Snudz can you please reply to https://bugs.documentfoundation.org/show_bug.cgi?id=159467#c10

[ovari]

@Snudz does that mean that LibreOffice 24.8 will solve this issue? If so, can you please close this issue?

[Snudz]

As far as I'm concerned, yes, 24.8 will fix my issue.