software

I have a problem with VLOOKUP looking in sorted lists of more than 16384 items.
Basically it fails at 16385th item

I have developed a VLOOKUP function that looks up in multiple tabs in
multiple files and returns a value. However I have discovered that it fails
at row 16385. As Excel copes to 64,000 rows I regard this as a bug. Has
anyone else encounteed this problem? is it just my machine, is there a
solution / bug fix?

If I use FALSE when the item to be found is beyond 16384 the function
returns the 16384th value this is a warning to other users who may not have
noticed as I did not initially.
--
A charming and handsome, highley intelectual adept levle Excel user.

I did a simple test, but I didn't see that problem myself. I could lookup
item 16385 on quite happily.

It sounds like something in your data. what are the values and lookup
values?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Bluewolfquot; gt; wrote in message
...
gt; I have a problem with VLOOKUP looking in sorted lists of more than 16384
items.
gt; Basically it fails at 16385th item
gt;
gt; I have developed a VLOOKUP function that looks up in multiple tabs in
gt; multiple files and returns a value. However I have discovered that it
fails
gt; at row 16385. As Excel copes to 64,000 rows I regard this as a bug. Has
gt; anyone else encounteed this problem? is it just my machine, is there a
gt; solution / bug fix?
gt;
gt; If I use FALSE when the item to be found is beyond 16384 the function
gt; returns the 16384th value this is a warning to other users who may not
have
gt; noticed as I did not initially.
gt; --
gt; A charming and handsome, highley intelectual adept levle Excel user.
My Data is OK or rather it is failing in more than one set of data.

VLOOKUP($T$4,'P:\[P.xls]PE'!$A:$B,2,FALSE)
The correct value if T4 is located on a row less than 16384 but Returns #N/A
if T4 is located beyond row 16384

Excel often comes up with error

Excel cannot complete this task with available resources. Chose less data or
close other applications- while I am replicating this formulea however the
above applies ie it finds a value if located in rows less than 16385 and #N/A
if greater than 16384

--
A charming and handsome, highley intelectual adept levle Excel user.quot;Bob Phillipsquot; wrote:

gt; I did a simple test, but I didn't see that problem myself. I could lookup
gt; item 16385 on quite happily.

Is the file you are trying to lookup from saved in Excel 5.0/95
version? This was limited to 16k rows.

Hope this helps.

PeteThanks Pete as I often save files in older versions to be compatible with
other peoples systems I thoulght you had it but unfortunatluy that was not
the case
--
A charming and handsome, highley intelectual adept levle Excel user.quot;Pete_UKquot; wrote:

gt; Is the file you are trying to lookup from saved in Excel 5.0/95
gt; version? This was limited to 16k rows.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt;

If I knew how I would put up a couple of files so the helpfull could see the
problem.
--
A charming and handsome, highley intelectual adept levle Excel user.quot;Bluewolfquot; wrote:

gt; I have a problem with VLOOKUP looking in sorted lists of more than 16384 items.
gt; Basically it fails at 16385th item
gt;
gt; I have developed a VLOOKUP function that looks up in multiple tabs in
gt; multiple files and returns a value. However I have discovered that it fails
gt; at row 16385. As Excel copes to 64,000 rows I regard this as a bug. Has
gt; anyone else encounteed this problem? is it just my machine, is there a
gt; solution / bug fix?
gt;
gt; If I use FALSE when the item to be found is beyond 16384 the function
gt; returns the 16384th value this is a warning to other users who may not have
gt; noticed as I did not initially.
gt; --
gt; A charming and handsome, highley intelectual adept levle Excel user.

I am most likely no better than you at troubleshooting your Vlookup problem,
but if you want I would look it over if you sent me an example workbook.

Regards,
Howard

quot;Bluewolfquot; gt; wrote in message
...
gt; If I knew how I would put up a couple of files so the helpfull could see
gt; the
gt; problem.
gt; --
gt; A charming and handsome, highley intelectual adept levle Excel user.
gt;
gt;
gt; quot;Bluewolfquot; wrote:
gt;
gt;gt; I have a problem with VLOOKUP looking in sorted lists of more than 16384
gt;gt; items.
gt;gt; Basically it fails at 16385th item
gt;gt;
gt;gt; I have developed a VLOOKUP function that looks up in multiple tabs in
gt;gt; multiple files and returns a value. However I have discovered that it
gt;gt; fails
gt;gt; at row 16385. As Excel copes to 64,000 rows I regard this as a bug. Has
gt;gt; anyone else encounteed this problem? is it just my machine, is there a
gt;gt; solution / bug fix?
gt;gt;
gt;gt; If I use FALSE when the item to be found is beyond 16384 the function
gt;gt; returns the 16384th value this is a warning to other users who may not
gt;gt; have
gt;gt; noticed as I did not initially.
gt;gt; --
gt;gt; A charming and handsome, highley intelectual adept levle Excel user.
Thank you howard I'll Email it to your adress registered here
--
A charming and handsome, highley intelectual adept levle Excel user.quot;L. Howard Kittlequot; wrote:

gt; I am most likely no better than you at troubleshooting your Vlookup problem,
gt; but if you want I would look it over if you sent me an example workbook.
gt;
gt; Regards,
gt; Howard
gt;
gt; quot;Bluewolfquot; gt; wrote in message
gt; ...
gt; gt; If I knew how I would put up a couple of files so the helpfull could see
gt; gt; the
gt; gt; problem.
gt; gt; --
gt; gt; A charming and handsome, highley intelectual adept levle Excel user.
gt; gt;
gt; gt;
gt; gt; quot;Bluewolfquot; wrote:
gt; gt;
gt; gt;gt; I have a problem with VLOOKUP looking in sorted lists of more than 16384
gt; gt;gt; items.
gt; gt;gt; Basically it fails at 16385th item
gt; gt;gt;
gt; gt;gt; I have developed a VLOOKUP function that looks up in multiple tabs in
gt; gt;gt; multiple files and returns a value. However I have discovered that it
gt; gt;gt; fails
gt; gt;gt; at row 16385. As Excel copes to 64,000 rows I regard this as a bug. Has
gt; gt;gt; anyone else encounteed this problem? is it just my machine, is there a
gt; gt;gt; solution / bug fix?
gt; gt;gt;
gt; gt;gt; If I use FALSE when the item to be found is beyond 16384 the function
gt; gt;gt; returns the 16384th value this is a warning to other users who may not
gt; gt;gt; have
gt; gt;gt; noticed as I did not initially.
gt; gt;gt; --
gt; gt;gt; A charming and handsome, highley intelectual adept levle Excel user.
gt;
gt;
gt;

Howard I have emailed the files to you have you recived them yet, if so have
you managed to take a look.
--
Howard Kittlequot; wrote:

gt; I am most likely no better than you at troubleshooting your Vlookup problem,
gt; but if you want I would look it over if you sent me an example workbook.
gt;
gt; Regards,
gt; HowardHi Bob,

I could send you two files one with data the other containing the lookup
functions if you would be willing to take a look at the problem.

Andrew

--
A charming and handsome, highley intelectual adept levle Excel user.quot;Bob Phillipsquot; wrote:

gt; I did a simple test, but I didn't see that problem myself. I could lookup
gt; item 16385 on quite happily.
gt;
gt; It sounds like something in your data. what are the values and lookup
gt; values?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Bluewolfquot; gt; wrote in message
gt; ...
gt; gt; I have a problem with VLOOKUP looking in sorted lists of more than 16384
gt; items.
gt; gt; Basically it fails at 16385th item
gt; gt;
gt; gt; I have developed a VLOOKUP function that looks up in multiple tabs in
gt; gt; multiple files and returns a value. However I have discovered that it
gt; fails
gt; gt; at row 16385. As Excel copes to 64,000 rows I regard this as a bug. Has
gt; gt; anyone else encounteed this problem? is it just my machine, is there a
gt; gt; solution / bug fix?
gt; gt;
gt; gt; If I use FALSE when the item to be found is beyond 16384 the function
gt; gt; returns the 16384th value this is a warning to other users who may not
gt; have
gt; gt; noticed as I did not initially.
gt; gt; --
gt; gt; A charming and handsome, highley intelectual adept levle Excel user.
gt;
gt;
gt;