Array in formula Vlookup changes when data list is added to
 

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.

Array in formula Vlookup changes when data list is added to
 

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.

Array in formula Vlookup changes when data list is added to
 

Yes this fixed it thanks MIke!

"Mike H" wrote:

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.

Array in formula Vlookup changes when data list is added to
 

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

Array in formula Vlookup changes when data list is added to
 

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.