vlookup size limitations search range?

[Hans]

Hi,

I'm experiencing problems with vlookup and I'm afraid it's linked to the
size of the range in which I look.

This is a table of 5 columns and approx 1300 rows. And records that are in
row 1200 (approx) and below return a #N/A when search for by vlookup. (I
tested this with changing the sorting from ascending to descending and then
looking again for the record I needed.)

I must say that the file in which the vlookup is called has 6 columns of
each approx 120 rows with vlookup functions.

I cannot imagine that this is already at/over the limits of excel? The
version I'm using is 2003 SP2

Can anyone help me out?

Thanks,

Hans

 

[Jim Cone]

Use absolute references $D$1:$D$1300 in the lookup table.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Hans"
wrote in message
Hi,
I'm experiencing problems with vlookup and I'm afraid it's linked to the
size of the range in which I look.

This is a table of 5 columns and approx 1300 rows. And records that are in
row 1200 (approx) and below return a #N/A when search for by vlookup. (I
tested this with changing the sorting from ascending to descending and then
looking again for the record I needed.)

I must say that the file in which the vlookup is called has 6 columns of
each approx 120 rows with vlookup functions.

I cannot imagine that this is already at/over the limits of excel? The
version I'm using is 2003 SP2
Can anyone help me out?
Thanks,
Hans

 

[Pete_UK]

Make sure that the table you are looking in covers the range of data,
eg A$1:F$1300 - perhaps you have it covering only up to 1200?

Hope this helps.

Pete

 

[Matt Richardson]

Use absolute references $D$1:$D$1300 in the lookup table.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Hans"
wrote in message
Hi,
I'm experiencing problems with vlookup and I'm afraid it's linked to the
size of the range in which I look.

This is a table of 5 columns and approx 1300 rows. And records that are in
row 1200 (approx) and below return a #N/A when search for by vlookup. (I
tested this with changing the sorting from ascending to descending and then
looking again for the record I needed.)

I must say that the file in which the vlookup is called has 6 columns of
each approx 120 rows with vlookup functions.

I cannot imagine that this is already at/over the limits of excel? The
version I'm using is 2003 SP2
Can anyone help me out?
Thanks,
Hans

Jim is right. Because you are using absolute references, the lookup
table is moving down as you go down the rows. When you reach a
certain point you'll start to get the #N/A errors because of this.
Absolute references will ensure that the table used doesn't move as
you move down the rows.

Regards,
Matt Richardson
http://teachr.blogspot.com

 

[Hans]

Thx, this did the trick.

Jim is right. Because you are using absolute references, the lookup
table is moving down as you go down the rows. When you reach a
certain point you'll start to get the #N/A errors because of this.
Absolute references will ensure that the table used doesn't move as
you move down the rows.

Regards,
Matt Richardson
http://teachr.blogspot.com