VLOOKUP - is there a maximum array size 
Author Message
 VLOOKUP - is there a maximum array size

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 approx.
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,

Pete



Wed, 18 Jun 1902 08:00:00 GMT  
 VLOOKUP - is there a maximum array size
Hi Pete,

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.

Groeten,

Niek Otten



Quote:
> 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
approx.
> 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,

> Pete



Wed, 18 Jun 1902 08:00:00 GMT  
 VLOOKUP - is there a maximum array size

Cheers Niek,

I was a 'dirty' data issue which was masked because the array is actually a
pivot table.  Thanks for your help.

Pete


Quote:
> Hi Pete,

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

> Groeten,

> Niek Otten



> > 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
> approx.
> > 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,

> > Pete



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Maximum array size?

2. Maximum array size

3. maximum array size

4. I am new learning VLOOKUP

5. Maximum Value inside of an array

6. Maximum number of cells in an array formula ?

7. Minimum and maximum value in an array?

8. Array Formula maximum Rows?

9. Identifying the column name of the maximum value from an array

10. vlookup: how to choose from among different table arrays


 
Powered by phpBB® Forum Software © phpBB Group