VLOOKUP - is there a maximum array size
Should work for arrays even of 64k rows. I guess one item around row 1640 is
out of order, maybe it looks allright but is really text instead of numeric
or, if they are all text, it contains unprintable characters or other
"dirt". You can find out by sorting the array.
All this applies only if the fourth parameter of Vlookup is set to TRUE.
Another possibility is that you use a defined name for the array and the
definition has not been extended. The definition will extend if you insert
rows *befor* the end of the current definition.
Of course the error might be in tour search argument as weel. To check,
insert a formula somewhere like =SearchArgument=ItemInTheList, where you
point at the actual two fields to compare.
> I've been successfully using VLOOKUP in a number of sheets for a while now
> and over time the sheets have grown so they are now about 1800 rows long.
> I've just realised that using these tables for a VLOOKUP function has
> stopped working towards the end, ie. VLOOKUP only seems to work for
> 1640 rows. Any lookup after this value just returns #N/A which is the
> reason why I missed the problem as this is not an error code.
> Is there a maximum size for lookup arrays?
> Is there any reg tweaks I can apply to extend it?
> Has anybody got a work around, besides splitting the sheets?
> In hope,