Function wizard causes lockup

[JSpeed]

the formula

=IF(C3<>"",IF(ISERROR(VLOOKUP(C3,‘Item List’.A:Z,2,0)),“New Item”,VLOOKUP(C3,‘Item List’.A:Z,2,0)),"")

causes a lockup unless there is not an “Item List” tab, or that tab is empty. With any information in the “Item List” tab, even one cell, opening Function Wizard takes a long time and if there is quite a bit of information, it locks up completely.

C3 is a bar code and “Item List” is a large database of bar coded items.

File created with Libreoffice 5.0, issue is with v.5.1.4.2. Having same issue with Windows 10 (2 machines) and Linux Mint KDE.

[erAck]

The expression =IF(C3<>"",IF(ISERROR(VLOOKUP(C3,'Item List'.A:Z,2,0)),"New Item",VLOOKUP(C3,'Item List'.A:Z,2,0)),"") needs to execute the lookup twice if successful. You can likely half the time by using =IF(C3<>"",IFNA(VLOOKUP(C3,'Item List'.A:Z,2,0),"New Item"),"") instead.

Also note that ISERROR in your formula suppresses any error, while IFNA matches only the #N/A error as a no-match result of VLOOKUP. Otherwise you would have seen that if the sheet ‘Item List’ did not exist the result was #NAME?

Using the Function Wizard with VLOOKUP (or HLOOKUP or MATCH or LOOKUP) on large data can be problematic, as the Function Wizard needs to invoke not only the functions of the expression but also obtain the intermediate data arrays and results.

[JSpeed]

I don’t understand how that new formula pulls the correct data, but it does. (I’m not an expert by any means). Thanks for simplifying the formula.

However, the same issue persists and it the window is “Not Responding” when I click the “Function Wizard” button for a long time before the wizard pops up. This is not an issue in v.5.0.6.

[erAck]

Since LibreOffice 5.1 the Function Wizard displays the intermediate results and operands of an expression on its Structure page, that’s probably why. Apparently there’s the need of some optimization.

The new formula works because IFNA() checks if the first argument is the #N/A error you get if VLOOKUP didn’t find a match, if it is not then it returns the already obtained VLOOKUP result, if it is a #N/A error then it returns the alternative value “New Item”.

[JSpeed]

Excellent information, thank you for your help.

As for the Function Wizard, I’ll back out to the Still version (5.0.6) and hope v.5.2 corrects the poor response.