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)

"Bluewolf" wrote in message
...
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.

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.


"Bob Phillips" wrote:

I did a simple test, but I didn't see that problem myself. I could lookup
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.

Pete

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


"Pete_UK" wrote:

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.

Pete

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.


"Bluewolf" wrote:

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

"Bluewolf" wrote in message
...
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.


"Bluewolf" wrote:

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.

Thank you howard I'll Email it to your adress registered here
--
A charming and handsome, highley intelectual adept levle Excel user.


"L. Howard Kittle" wrote:

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

"Bluewolf" wrote in message
...
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.


"Bluewolf" wrote:

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.

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

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

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


"Bob Phillips" wrote:

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)

"Bluewolf" wrote in message
...
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.

Okay, send it over.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bluewolf" wrote in message
...
Hi 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.


"Bob Phillips" wrote:

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)

"Bluewolf" wrote in message
...
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.

Sorry for not getting back to you. I did not fully understand the
worksheet. And with that said, did not see anything obvious.

Hopefully, Bob will spot something. Good luck.

Regards,
Howard

"Bluewolf" wrote in message
...
Howard I have emailed the files to you have you recived them yet, if so
have
you managed to take a look.
--
Howard Kittle" wrote:

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