Ask Your Question
0

VLOOKUP issue (data limit?)

asked 2018-09-20 04:16:45 +0200

Lord Vltor gravatar image

updated 2018-09-21 02:44:43 +0200

Hello, seems I can't get VLOOKUP work with a dropdown menu of 16 elements, since the VLOOKUP outputs an "#N/A" as result everytime I select, from my dropdown menu, the 9th (in one situation) or 10th (in another) element of the list. Accordingly to this thread (https://ask.libreoffice.org/en/questi...), Calc has a limit when handling VLOOKUP, but this limit is over 2k rows, while I have this issue when I do not eve hit the 10th. I have reviewed the formula over and over and I can't find the error, if there's any (and there must be one, after all, if the output is "#N/A"). Thank you for the help you will provide.

EDIT 180921-02:27CET: apparently, on a search, only THREE entries are broken (two sequentials, and the last one). On the other search, at least TWO out of 16 are not working (the one I need, and one two selections after). I do not understand why there's this yes/no working thing where it should just work or not. Attaching the (someway) compiled file to help you sort things out. C:\fakepath\LV's OGame Manager v180920-LV.ods The VLOOKUPs not working are: TAB "BunkervsRIP", "Chosen Planet": "Imperivm" and "Legio X" TAB "Planner", "Fake Research": "Computertech", "Astrophysics" and "Armour"

edit retag flag offensive close merge delete

Comments

Can you please provide sample document to let us help you find an error, if there is one. N/A means there is no entry matching search criteria, not some kind of performance issue. Calc performance is mostly limited by your hardware, I deal with ~100k Vlookup rows on powerful machine at work, but my old home computer can hardly fill 15-20k rows.

SM_Riga gravatar imageSM_Riga ( 2018-09-20 06:37:35 +0200 )edit

Thanks for providing sample document. The problem source is VLOOKUP() 4th parameter omitting. You should add 0 as 4th parameter in your VLOOKUP() formulas, exactly as @erAck proposed. For example, changing $BunkervsRIP.G15 to VLOOKUP($AP$3,$MAIN.$C$61:$AK$76,7,0) gives now 112,750 for Imperivm, not #N/A. Please reffer to this Q&A for more information on VLOOKUP VLOOKUP questions

SM_Riga gravatar imageSM_Riga ( 2018-09-21 07:09:56 +0200 )edit

IT WORKED! Many thanks for that - I'm just still wondering WHY the 4th parameter missing turns out into an output error only for SOME records, but... oh well, now it works :p

Lord Vltor gravatar imageLord Vltor ( 2018-09-23 01:39:57 +0200 )edit

Default value for 4th parameter is 1, so when it is omitted, VLOOKUP takes lookup range as an ascendingly sorted array and performs binary search In a few words, as first step VLOOKUP takes middle cell from the lookup range and compares it with the value you want to find. If it is matched, then VLOOKUP returns result.

SM_Riga gravatar imageSM_Riga ( 2018-09-23 11:24:47 +0200 )edit

If value to search is smaller than middle cell’s value, VLOOKUP assumes that needed value is in the first half of range (which is obvious if the array is sorted ascendingly), if value is bigger - VLOOKUP assumes that this value is in the second half. So function performs same kind of search on half of array, takes the middle point, compares to searchable value, if not matched, decides on which half to perform search again and so on while the result is matched.

SM_Riga gravatar imageSM_Riga ( 2018-09-23 11:33:23 +0200 )edit

As you know from function description, If there is no perfect match, VLOOKUP with 4th parameter “1” returns the closest value, which is less than value to search (searching for 3.14 in array {1|2|3|4} will return 3). So here comes the interesting part - if you perform a binary search on an unsorted array, your value to search may happen to be the smallest in the range after final step and VLOOKUP can not return any match, so it returns N/A.

SM_Riga gravatar imageSM_Riga ( 2018-09-23 11:48:07 +0200 )edit

For example, if you search for 1 in unsorted array {9|7|8|6|5|3|4|2|1} with 4th parameter “1” or omitted, VLOOKUP takes middle of an array, which is 5, finds that 1 is less than 5, decides that value needed is in first half of an array, which is true for sorted array, but not for ours, cause here the half left is {9|7|8|6}. VLOOKUP runs test on the middle value again, finds 1 is less than 7 or 8, so the only value left is 9, which is also larger than 1. No value is matched, function returns N/A,

SM_Riga gravatar imageSM_Riga ( 2018-09-23 12:04:07 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2018-09-20 17:27:50 +0200

erAck gravatar image

There is no limit on the number of rows VLOOKUP() can handle. If you get a #N/A or other unexpected result then make sure that if you are using the range lookup (4th parameter Sort order is not given or not 0) the data range is strictly sorted, or specify 0 as 4th parameter for an exact lookup. Also note that VLOOKUP() supports wildcards or regular expressions, whatever is activated under Tools -> Options -> Calc -> Calculate, specifically if regular expressions are enabled and your search term contains regexp metacharacters or operators (see ICU Regular Expressions) that are to be found literally, the result may be unexpected if they are not properly escaped.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-09-20 04:16:45 +0200

Seen: 117 times

Last updated: Sep 21 '18