VLOOKUP

Posted by Ed on July 20, 2001 8:21 AM

Does the VLOOKUP Function have a limit to the number of rows it will search? For some reason it will not look past row 16,375. The file has 29,456 rows. The Function works fine before row 16,375.

Thanks for any help,

Posted by Barrie Davidson on July 20, 2001 8:25 AM

Ed, can you post your formula please.

Barrie

Posted by Ed on July 20, 2001 8:29 AM

Barrie,

The Formula is:=VLOOKUP($C25,'G:\Parts List\[Master 01.xls]BOOK1'!$A$1:$H$29742,2,FALSE)

Thanks,

Posted by Barrie Davidson on July 20, 2001 8:32 AM

Ed, what kind of problem are you having? From what I can tell, this should work fine for you. How do you know the formula will not look past row 16,375?

Regards,
Barrie

Posted by Ed on July 20, 2001 8:42 AM

Barrie,

The problem is the cell where the Formula is in does not change when I type the part number to lookup in column C. I figured out it was row 16,375 by the process of elimination. What a pain.

Thanks,

Posted by Barrie Davidson on July 20, 2001 9:09 AM

Ed, I'm not trying to insult your intelligence but, you don't have calculation set to manual do you?

Also, I am aware of a problem in Excel97 where the spreadsheet does not calculate sometimes (even if you have options set to automatic calculation). You can confirm if this is happening by selecting your cell, pressing F2, then ENTER - your cell will change. If this is happening try CTRL+ALT+F9 to force Excel to calculate.

Barrie

Posted by Ed on July 20, 2001 9:23 AM

Barrie,

The calculation is set to automatic. If I enter the cell, press F2, and hit enter, it runs for a little bit and then gives me an error message(out of memory). I think that may be the problem. I will explore some ways to downsize the files.

Thanks,