Array in formula Vlookup changes when data list is added to

[Wileyb]

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.

 

[Mike H]

Maybe this,

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

Mike

 

[Wileyb]

Yes this fixed it thanks MIke!

Maybe this,

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

Mike

 

[T. Valko]

=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)

 

[Mike H]

good point 2 keystrokes duly saved

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)