more efficient way to lookup a range?
 

At work we use Excel all the time, and many of our spreadsheets use
INDEX-MATCH formulas to pull in stock quotes from other workbooks saved
on our network. In order to avoid a problem of having rows in the
index fall outside of the range, our formulas define the index in terms
of entire columns, e.g. A:H. (We don't even get close to using all the
rows, but out of habit in our dept, and for simplicity we use the
entire column. A1:H2500 is probably more than enough, but old habits
die hard.)

Would it be more efficient if the formulas referred to an
A1:OFFSET/COUNTIF... type formula to only refer down as far as needed,
based on the number of rows actually used? I understand that certain
worksheet functions/arrays can slow down the sheet, but I don't know
which ones are the main offenders. Any ideas? Is it worth it to
rework our formulas?

We havent had too many complaints about slow workbooks, but I'd like to
know for future reference. I'm always looking for a way to make
("build"?) my spreadsheets better, more efficient, bulletproof,
yada-yada. Thanks!

more efficient way to lookup a range?
 

Offset is a volatile function and will recalculate every time Excel
recalculates, which can slow things down. I found a list in a previous post
by Peo Sjoblom of some volatile functions.

OFFSET()
CELL()
INDIRECT()
INFO()
NOW()
TODAY()
RAND()


" wrote:

At work we use Excel all the time, and many of our spreadsheets use
INDEX-MATCH formulas to pull in stock quotes from other workbooks saved
on our network. In order to avoid a problem of having rows in the
index fall outside of the range, our formulas define the index in terms
of entire columns, e.g. A:H. (We don't even get close to using all the
rows, but out of habit in our dept, and for simplicity we use the
entire column. A1:H2500 is probably more than enough, but old habits
die hard.)

Would it be more efficient if the formulas referred to an
A1:OFFSET/COUNTIF... type formula to only refer down as far as needed,
based on the number of rows actually used? I understand that certain
worksheet functions/arrays can slow down the sheet, but I don't know
which ones are the main offenders. Any ideas? Is it worth it to
rework our formulas?

We havent had too many complaints about slow workbooks, but I'd like to
know for future reference. I'm always looking for a way to make
("build"?) my spreadsheets better, more efficient, bulletproof,
yada-yada. Thanks!