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

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" <Fred@discussions.microsoft.com> wrote in message
news:5F7191BA-F2C3-43D1-922C-51F8BC9A95F7@microsoft.com...
> 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
>
>

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

Howard

"Fred" <Fred@discussions.microsoft.com> wrote in message
news:5F7191BA-F2C3-43D1-922C-51F8BC9A95F7@microsoft.com...
> 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
>
>

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