good point 2 keystrokes duly saved

"T. Valko" wrote:

=VLOOKUP(F1,INDIRECT("$A$1:$E$40"),5,FALSE)

You can save a few keystrokes by eliminating the $ signs.

=VLOOKUP(F1,INDIRECT("A1:E40"),5,FALSE)

When the argument to INDIRECT is a straight TEXT string the references will
never change if the formula is moved/copied/rows/columns inserted. Also,
INDIRECT passes its argument to VLOOKUP as an absolute reference.

=VLOOKUP(F1,INDIRECT("A1:E40"),5,FALSE)

Evaluates to:

=VLOOKUP(F1,$A$1:$E$40,5,FALSE)


--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Maybe this,

=VLOOKUP(F1,INDIRECT("$A$1:$E$40"),5,FALSE)

Mike

"Wileyb" wrote:

How do I keep the array absolute in my formula when data is added to the
list
that it refers to? "A$1$:E$40$" does not work. When data is moved in the
list, ie; when sorted, the array referenced in Vlookup moves with the
original data.