Array in formula Vlookup changes when data list is added to

W

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

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

Mike H

good point 2 keystrokes duly saved

T. Valko said:
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)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP duplicate values in table the table array? Help me solve 1
Vlookup error 1
Need help to update a column when Drop Down List is changed 0
VLookup Won't Copy down (#REF! error) 1
VLOOKUP & Data Validation 0
VLOOKUP & non-contiguous table array 3
using strings in vlookup function calls 5
Vlookup - multiple tabs 5

Top