Help needed to understand Vlookup Formula

[Steve Foy]

hi,

I've just taken over someones job and I have a problem with a couple of
Excel 2000 spreadsheets. One of the spreadsheets picks up some data from
another one using a vlookup formula. The formula is below but could someone
please tell me what it is actually looking for by breaking down the
parameters:

=VLOOKUP(V11,'C:\Deliveries\[Buy Del.xls]Buy Delivery
Schedule'!$U$6:$AC$552,9,FALSE)

When this spreadsheet is opened and the vaules updated from the other
spreadsheet it is linked to I also get an "out of memory error". Both
spreadsheets are quite small and my PC is P4 512MB RAM. I was wondering if
this vlookup is causing the problem.

many thanks.

 

[Ken Wright]

The VLOOKUP takes whatever value you have in V11, and then goes and looks for it in Column U in
the table $U$6:$AC$552 in the file 'C:\Deliveries\[Buy Del.xls]Buy Delivery Schedule'. Once it
finds it, it will then go to the 9th column in that table (Hence the 9) and return the value in
the same row as the row it found the match for V11. The FALSE argument means that it must find an
exact match in Column U if it is to return anything, as there are instances when you would just
want the closest match and not an exact match.

Sounds like your machine has more than enough resources to cope with a couple of small
spreadsheets, though it is not just system memory that limits Excel, but I'm not sure why you
would have that error to be honest. How many links in the file, do you know? What size files?

 

[Steve]

hi,

thanks for the reply. The files sizes are 734KB for the one containing the
vlookup and 8MB for the Buy Del.xls file. Looking at the Links in the
vlookup file there is only one - pointing to the Buy Del.xls file, no links
in Buy Del.xls. Should this link be there? or is it put there automatically
when you reference the file using vlookup.
Many thanks for your help.

The VLOOKUP takes whatever value you have in V11, and then goes and looks for it in Column U in
the table $U$6:$AC$552 in the file 'C:\Deliveries\[Buy Del.xls]Buy Delivery Schedule'. Once it
finds it, it will then go to the 9th column in that table (Hence the 9) and return the value in
the same row as the row it found the match for V11. The FALSE argument means that it must find an
exact match in Column U if it is to return anything, as there are instances when you would just
want the closest match and not an exact match.

Sounds like your machine has more than enough resources to cope with a couple of small
spreadsheets, though it is not just system memory that limits Excel, but I'm not sure why you
would have that error to be honest. How many links in the file, do you know? What size files?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

-------------------------------------------------------------------------- --
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------------------------- --

hi,

I've just taken over someones job and I have a problem with a couple of
Excel 2000 spreadsheets. One of the spreadsheets picks up some data from
another one using a vlookup formula. The formula is below but could someone
please tell me what it is actually looking for by breaking down the
parameters:

=VLOOKUP(V11,'C:\Deliveries\[Buy Del.xls]Buy Delivery
Schedule'!$U$6:$AC$552,9,FALSE)

When this spreadsheet is opened and the vaules updated from the other
spreadsheet it is linked to I also get an "out of memory error". Both
spreadsheets are quite small and my PC is P4 512MB RAM. I was wondering if
this vlookup is causing the problem.

many thanks.

 

[Ken Wright]

There is no link in the Buy Del file, and nor should there be. The link exists only in the
workbook with the formula in it that contains the link, ie your 743KB file. Is this problem
peculiar to yourself and your machine, or does it happen to others. Also what about whoever you
inherited it from, did they have similar problems at all?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

hi,

thanks for the reply. The files sizes are 734KB for the one containing the
vlookup and 8MB for the Buy Del.xls file. Looking at the Links in the
vlookup file there is only one - pointing to the Buy Del.xls file, no links
in Buy Del.xls. Should this link be there? or is it put there automatically
when you reference the file using vlookup.
Many thanks for your help.

The VLOOKUP takes whatever value you have in V11, and then goes and looks for it in Column U in
the table $U$6:$AC$552 in the file 'C:\Deliveries\[Buy Del.xls]Buy Delivery Schedule'. Once it
finds it, it will then go to the 9th column in that table (Hence the 9) and return the value in
the same row as the row it found the match for V11. The FALSE argument means that it must find an
exact match in Column U if it is to return anything, as there are instances when you would just
want the closest match and not an exact match.

Sounds like your machine has more than enough resources to cope with a couple of small
spreadsheets, though it is not just system memory that limits Excel, but I'm not sure why you
would have that error to be honest. How many links in the file, do you know? What size files?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

-------------------------------------------------------------------------- --
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------------------------- --

hi,

I've just taken over someones job and I have a problem with a couple of
Excel 2000 spreadsheets. One of the spreadsheets picks up some data from
another one using a vlookup formula. The formula is below but could someone
please tell me what it is actually looking for by breaking down the
parameters:

=VLOOKUP(V11,'C:\Deliveries\[Buy Del.xls]Buy Delivery
Schedule'!$U$6:$AC$552,9,FALSE)

When this spreadsheet is opened and the vaules updated from the other
spreadsheet it is linked to I also get an "out of memory error". Both
spreadsheets are quite small and my PC is P4 512MB RAM. I was wondering if
this vlookup is causing the problem.

many thanks.

 

[Steve]

hi,

the problem exists even if I copy both Excel files to my PC at home (running
Excel 2003). I had no feed back form the person as they left the company
before I arrived.

There is no link in the Buy Del file, and nor should there be. The link exists only in the
workbook with the formula in it that contains the link, ie your 743KB file. Is this problem
peculiar to yourself and your machine, or does it happen to others. Also what about whoever you
inherited it from, did they have similar problems at all?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

-------------------------------------------------------------------------- --
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------------------------- --

hi,

thanks for the reply. The files sizes are 734KB for the one containing the
vlookup and 8MB for the Buy Del.xls file. Looking at the Links in the
vlookup file there is only one - pointing to the Buy Del.xls file, no links
in Buy Del.xls. Should this link be there? or is it put there automatically
when you reference the file using vlookup.
Many thanks for your help.

The VLOOKUP takes whatever value you have in V11, and then goes and

looks
for it in Column U in

the table $U$6:$AC$552 in the file 'C:\Deliveries\[Buy Del.xls]Buy Delivery Schedule'. Once it
finds it, it will then go to the 9th column in that table (Hence the

9)
and return the value in

the same row as the row it found the match for V11. The FALSE

argument
means that it must find an

exact match in Column U if it is to return anything, as there are instances when you would just
want the closest match and not an exact match.

Sounds like your machine has more than enough resources to cope with a couple of small
spreadsheets, though it is not just system memory that limits Excel,

but
I'm not sure why you

would have that error to be honest. How many links in the file, do

you
know? What size files?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------------------------------------------------------------------------------

--



hi,

I've just taken over someones job and I have a problem with a couple of
Excel 2000 spreadsheets. One of the spreadsheets picks up some data from
another one using a vlookup formula. The formula is below but could someone
please tell me what it is actually looking for by breaking down the
parameters:

=VLOOKUP(V11,'C:\Deliveries\[Buy Del.xls]Buy Delivery
Schedule'!$U$6:$AC$552,9,FALSE)

When this spreadsheet is opened and the vaules updated from the other
spreadsheet it is linked to I also get an "out of memory error". Both
spreadsheets are quite small and my PC is P4 512MB RAM. I was

wondering
if

this vlookup is causing the problem.

many thanks.

 

[Steve]

hi,

I had a further look at the Buy Del spreadsheet and found that if I deleted
from a certain column to the last column the update would work. The Buy Del
spreadsheet consists of Columns A1-IV, rows 1-10086 (Cntl-End - row 624 is
the last actual row with any data).
If I delete columns GP-IV the update link on the other spreadsheet would
complete successfully. If I leave the Buy Del spreadsheet as is or just
delete columns GQ-IV or GS-IV or HA-IV I get the "out of memory" or "Excel
cannot complete this task with available resources".

Another wierd thing is that if I delete these columns I would have thought
the file size would go down but it actually increases to over 18MB in size
(from 8MB). Does anyone know what could be causing these issues?. The
columns I deleted do not contain any formulas just plain data.

hi,

the problem exists even if I copy both Excel files to my PC at home (running
Excel 2003). I had no feed back form the person as they left the company
before I arrived.

There is no link in the Buy Del file, and nor should there be. The link exists only in the
workbook with the formula in it that contains the link, ie your 743KB file. Is this problem
peculiar to yourself and your machine, or does it happen to others.

Also
what about whoever you

inherited it from, did they have similar problems at all?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

--------------------------------------------------------------------------
--
Attitude - A little thing that makes a BIG difference
--------------------------------------------------------------------------
--

hi,

thanks for the reply. The files sizes are 734KB for the one containing the
vlookup and 8MB for the Buy Del.xls file. Looking at the Links in the
vlookup file there is only one - pointing to the Buy Del.xls file, no links
in Buy Del.xls. Should this link be there? or is it put there automatically
when you reference the file using vlookup.
Many thanks for your help.

The VLOOKUP takes whatever value you have in V11, and then goes and looks
for it in Column U in
the table $U$6:$AC$552 in the file 'C:\Deliveries\[Buy Del.xls]Buy
Delivery Schedule'. Once it
finds it, it will then go to the 9th column in that table (Hence the 9)
and return the value in
the same row as the row it found the match for V11. The FALSE argument
means that it must find an
exact match in Column U if it is to return anything, as there are
instances when you would just
want the closest match and not an exact match.

Sounds like your machine has more than enough resources to cope with a
couple of small
spreadsheets, though it is not just system memory that limits Excel, but
I'm not sure why you
would have that error to be honest. How many links in the file, do you
know? What size files?

----------------------------------------------------------------------------------------------------------------------------------------------------

--



hi,

I've just taken over someones job and I have a problem with a

couple

of

data

from

another one using a vlookup formula. The formula is below but could
someone
please tell me what it is actually looking for by breaking down the
parameters:

=VLOOKUP(V11,'C:\Deliveries\[Buy Del.xls]Buy Delivery
Schedule'!$U$6:$AC$552,9,FALSE)

When this spreadsheet is opened and the vaules updated from the other
spreadsheet it is linked to I also get an "out of memory error". Both
spreadsheets are quite small and my PC is P4 512MB RAM. I was wondering
if
this vlookup is causing the problem.

many thanks.