Ask Your Question

function wizard causes lockup

asked 2016-07-14 15:10:17 +0100

JSpeed gravatar image

updated 2016-07-14 15:16:22 +0100

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. Having same issue with Windows 10 (2 machines) and Linux Mint KDE.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-07-14 20:14:48 +0100

erAck gravatar image

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.

edit flag offensive delete link more


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.

JSpeed gravatar imageJSpeed ( 2016-07-14 21:27:02 +0100 )edit

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

erAck gravatar imageerAck ( 2016-07-14 21:41:17 +0100 )edit

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.

JSpeed gravatar imageJSpeed ( 2016-07-14 22:01:52 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-07-14 15:10:17 +0100

Seen: 53 times

Last updated: Jul 14 '16