vlookup question - variable table_array variables

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

Hi all,

I have a vlookup function that looks up values in a very large table,
the table_array is $a$10:$dp$51777 and so when I am looking up a
value at the bottom of the table, calculation time is very slow.

If I'm looking up a value on line 28666, I would like to be able to
have my table array be $a$28666:$dp$51777 as this would shorten my
re calc. time considerably, the lookup would always find the lookup_value
on the first line !!

So, is there a way to have the table_array string in the vlookup function as
a
variable value or something that will evaluate to the appropriate string ??

Thanks.
 
Hi

=VLOOKUP(X2866,$A2866:$dp$51777,n,FALSE)
where X is column with key values you search lookup table for, and n is
number of colum in lookup range containing return value. I.e. use relative
reference for row part of lookup ranges top - the formula will adjust, when
copied.


Arvi Laanemets
 
Neil said:
Hi all,

I have a vlookup function that looks up values in a very large table,
the table_array is $a$10:$dp$51777 and so when I am looking up a
value at the bottom of the table, calculation time is very slow.

If I'm looking up a value on line 28666, I would like to be able to
have my table array be $a$28666:$dp$51777 as this would shorten my
re calc. time considerably, the lookup would always find the lookup_value
on the first line !!
Click to expand...

I think I must be missing the point. If you know on what line your result is
to be found, you don't need a lookup function; you just return the value
from a particular column of that line. The whole purpose of a VLOOKUP is to
find the appropriate line.
 
Thanks for this link,

I actually used the offset function in a named range, which I then called up
as the
table_array value in my vlookup function.

Execution speed is dramatically quicker.

Thanks again
 
Back
Top