LOOKUP ARRY

[Lookup formula limitation?]

I have 160 number in column A and 160 numbers in Column B.....

Lookup let me to compair only 80?
=lookpu(A1,{1,2,3,4,5.........160},{10,20,30,............1600})

How come excel let me do up to 80, not 160?

 

[JLatham]

I haven't found "the" definitive answer to your question, but we can infer an
answer based on other similar limitations in Excel.

Various formulas have limits to the number of parameters they can handle.
For example, the CONCATENATE() function has a limit of 32 parameters. There
is also a limit to other things such as the length of a formula and levels of
nesting.

I suspect you've found the undocumented(?) limit to the number of parameters
in LOOKUP() as you've tried to use it.

As smartin indicated, for large lists like that you're probably better off
setting up some columns as a table, either on the same sheet or another, and
using VLOOKUP() to get your results.

 

[Dave Peterson]

What version of excel are you using?

In xl2003, your formulas can only be 1024 characters long (when measured in R1C1
reference style).

 

[Pete_UK]

In your example it looks like you want to multiply by 10 - can't you
just do:

=A1*10

or is your example not representative?

Pete

 

[Lookup formula limitation?]

I have 300 products range from 1lb to 200lb, each have differnt shipping price
Example:
A: has 1-160
B: has price for each items, is there a better way to figure out the
price/items?

Thanks

 

[JLatham]

I have a sneaking suspicion that it's not representative of the real
situation, but may be one that he used to try to determine where it was
failing at.
The root cause of the problem may even be something we've not mentioned yet,
such as stack or buffer size limits for the arrays involved when LOOKUP() is
used in this fashion - which would make his determined limit of 80 elements
not a constant, but a variable based on the type of elements in the arrays.

 

[Lookup formula limitation?]

Thanks,
But, how do you fix the limitation?

I have a sneaking suspicion that it's not representative of the real
situation, but may be one that he used to try to determine where it was
failing at.
The root cause of the problem may even be something we've not mentioned yet,
such as stack or buffer size limits for the arrays involved when LOOKUP() is
used in this fashion - which would make his determined limit of 80 elements
not a constant, but a variable based on the type of elements in the arrays.

 

[JLatham]

Well, lets look at your original statement...
"I have 160 numbers in 160 numbers in column B..."

There is no need to repeat those numbers within the LOOKUP statement - all
you need to do is reference the ranges involved. Since you are using A1 as a
lookup value, I will assume that your lists start on row 2. If that's the
case, then a formula like
=LOOKUP(A1,A$2:A$161,B$2:B$161)
will give you the results you want/expect - as long as the values in column
A are in ascending order.

If there is a possibility of the data in column A being out of order, you're
better off with a VLOOKUP()
=VLOOKUP(A1,A$2:B$161,2,False)
which will achieve the same thing without the items in column a needing to
be in an ordered sequence.