VLOOKUP in large Data sets of more than 16384 rows

B

Bluewolf

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

Bob Phillips

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

Pete_UK

I've also used VLOOKUP with far more rows than this and not encountered
problems. Can you post your formula here? Could it be that you have not
used absolute addressing for the table references?

Pete
 
B

Bluewolf

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
 
Y

Yools

I am having the exact same problem. I tried both -lookup- and -vlookup-
to return values from a spreadsheet with >50,000 lines, and with both I
seem to get the correct values, but when I save the book or try to copy
sheets I get the following error (see attached).

The sheet seems to work, but this error is making it difficult to
manage the workbook. Any ideas?

-Yools


+-------------------------------------------------------------------+
|Filename: ExcelError.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4532 |
+-------------------------------------------------------------------+
 
P

Pete_UK

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
 
B

Bluewolf

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
 
B

Bluewolf

If I knew how I would put up a couple of files so the helpfull could see the
problem.
 
L

L. Howard Kittle

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
 
B

Bluewolf

Howard I have emailed the files to you have you recived them yet, if so have
you managed to take a look.
 
B

Bluewolf

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
 
B

Bob Phillips

Okay, send it over.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
L

L. Howard Kittle

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
 
Top