Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Fred
 
Posts: n/a
Default vlookup maximum cell range?

Good day all,

I am trying to perform a vlookup in a (sorted) list of 705 items;

=VLOOKUP("D$2$",Business_Data!A$1:C$705,2)

However, whenever I add more than 300 in my lookup range, it returns an
incorrect value;

IE: if I do =VLOOKUP("D$2$",Business_Data!A$1:C$300,2), it returns the
proper value...

If I do =VLOOKUP("D$2$",Business_Data!A$1:C$301,2), it returns incorect
data...

is there a maximum range of cell to use? If there is one, what would you
suggest I use to lookup in my list of 700+ items?

Thanks & Regards


  #2   Report Post  
Posted to microsoft.public.excel.misc
L. Howard Kittle
 
Posts: n/a
Default vlookup maximum cell range?

Hi Fred,

A little hard to say for sure, but I would start with the using FALSE in the
4th argument in your formula.

Your range is set to absolute in the formulas you show here, so I assume
they are in your sheet. If not, that may be the problem.

HTH
Regards,
Howard

"Fred" wrote in message
...
Good day all,

I am trying to perform a vlookup in a (sorted) list of 705 items;

=VLOOKUP("D$2$",Business_Data!A$1:C$705,2)

However, whenever I add more than 300 in my lookup range, it returns an
incorrect value;

IE: if I do =VLOOKUP("D$2$",Business_Data!A$1:C$300,2), it returns the
proper value...

If I do =VLOOKUP("D$2$",Business_Data!A$1:C$301,2), it returns incorect
data...

is there a maximum range of cell to use? If there is one, what would you
suggest I use to lookup in my list of 700+ items?

Thanks & Regards




  #3   Report Post  
Posted to microsoft.public.excel.misc
L. Howard Kittle
 
Posts: n/a
Default vlookup maximum cell range?

Forgot to mention, there is no real limit for vlookup. I've heard posters
mention having 8 to 15 thousand.

Howard

"Fred" wrote in message
...
Good day all,

I am trying to perform a vlookup in a (sorted) list of 705 items;

=VLOOKUP("D$2$",Business_Data!A$1:C$705,2)

However, whenever I add more than 300 in my lookup range, it returns an
incorrect value;

IE: if I do =VLOOKUP("D$2$",Business_Data!A$1:C$300,2), it returns the
proper value...

If I do =VLOOKUP("D$2$",Business_Data!A$1:C$301,2), it returns incorect
data...

is there a maximum range of cell to use? If there is one, what would you
suggest I use to lookup in my list of 700+ items?

Thanks & Regards




  #4   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default vlookup maximum cell range?

"Fred" wrote:
I am trying to perform a vlookup in a (sorted) list of 705 items;
=VLOOKUP("D$2$",Business_Data!A$1:C$705,2)
However, whenever I add more than 300 in my lookup range, it returns an
incorrect value;
IE: if I do =VLOOKUP("D$2$",Business_Data!A$1:C$300,2), it returns the
proper value...
If I do =VLOOKUP("D$2$",Business_Data!A$1:C$301,2), it returns incorect
data...

is there a maximum range of cell to use? If there is one, what would you
suggest I use to lookup in my list of 700+ items?


One guess ..

Try an exact* VLOOKUP instead:
=VLOOKUP("D$2$",Business_Data!A$1:C$705,2,0)
*with 4th param set to zero, or FALSE
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
checking that cells have a value before the workbook will close kcdonaldson Excel Worksheet Functions 8 December 5th 05 05:57 PM
cell address rather than range name Angi Bemiss Excel Discussion (Misc queries) 1 December 1st 05 01:46 AM
Date Range within one cell Cachod1 New Users to Excel 5 October 18th 05 03:30 AM
macro help thephoenix12 Excel Discussion (Misc queries) 4 July 15th 05 05:57 PM
Reveal cell formats and extendable range in tool/statusbar/icon. Danny O'Hern ([email protected]) Excel Worksheet Functions 0 April 29th 05 01:16 PM


All times are GMT +1. The time now is 12:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"