Array in formula Vlookup changes when data list is added to

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wileyb - 23 Mar 2008 13:00 GMT

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.

Reply to this Message

Mike H - 23 Mar 2008 13:27 GMT

Maybe this,

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

Mike

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

Reply to this Message

Wileyb - 23 Mar 2008 15:01 GMT

Yes this fixed it thanks MIke!

> Maybe this,
>
[quoted text clipped - 6 lines]
> > list, ie; when sorted, the array referenced in Vlookup moves with the
> > original data.

Reply to this Message

T. Valko - 23 Mar 2008 18:02 GMT

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

Signature

Biff
Microsoft Excel MVP

> Maybe this,
>
[quoted text clipped - 7 lines]
>> list, ie; when sorted, the array referenced in Vlookup moves with the
>> original data.

Reply to this Message

Mike H - 23 Mar 2008 18:56 GMT

good point 2 keystrokes duly saved

> > =VLOOKUP(F1,INDIRECT("$A$1:$E$40"),5,FALSE)
>
[quoted text clipped - 23 lines]
> >> list, ie; when sorted, the array referenced in Vlookup moves with the
> >> original data.

Reply to this Message