Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup size limitations search range?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup size limitations search range?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup size limitations search range?
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 On Feb 18, 2:50*pm, Hans wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup size limitations search range?
On Feb 18, 3:32 pm, "Jim Cone" wrote:
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup size limitations search range?
Thx, this did the trick.
"Matt Richardson" wrote: On Feb 18, 3:32 pm, "Jim Cone" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAN VLOOKUP SEARCH AN ENTIRE WORKSHEET INSTEAD OF RANGE OF CELLS | Excel Worksheet Functions | |||
Size/memory limitations for vlookup fixed in the new Excel? | Excel Discussion (Misc queries) | |||
CAN YOU DYNAMICALY CHANGE VLOOKUP SEARCH RANGE? | Excel Discussion (Misc queries) | |||
size limitations on auto filter | Excel Discussion (Misc queries) | |||
Cell Limitations ? Formatting and Size when exporting to Tab based Text file? | Excel Worksheet Functions |