Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP does not work with array formula axb Excel Worksheet Functions 1 February 6th 08 09:09 PM
Vlookup+ Select Next Earliest Value (Array formula?) [email protected] Excel Worksheet Functions 1 November 6th 07 04:59 AM
array vlookup formula help michelle Excel Discussion (Misc queries) 2 February 23rd 07 06:44 PM
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM


All times are GMT +1. The time now is 07:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"