VLOOKUP issue (data limit?)

[LordVltor]

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 (Vlookup not working on large data), 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.
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”

[SM_Riga]

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]

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

[LordVltor]

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

[SM_Riga]

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]

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]

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]

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,

[erAck]

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.