VLOOKUP With static Table_Array

**clong** · 08-21-2008, 07:57 PM

I was wondering if any one knew how to make the Table_array section of a vlookup function static. For example my curect formula looks like this:
=VLOOKUP(A1,S1:T1000,2)
Usage - =VLOOKUP(Lookup_value,Table_Array,Col_index_num)

So, when I use the cell fill feature it is sequencing the numbers for the Lookup_value but also the Table_Array. Is there any way for this table array to stay static?
Thanks

**Paul** · 08-21-2008, 08:11 PM

clong, welcome to the forum!

To change a cell reference from relative to static, add $'s in the cell address, e.g.

=VLOOKUP(A1,$S$1:$T$1000,2)

A $ in front of the column letter will freeze the column, while a $ in front of the row number will freeze the row. (In front of both freezes both, making it completely static.)

Hope that helps!

**daddylonglegs** · 08-21-2008, 08:12 PM

You can use $ signs to "fix" references, e.g.

=VLOOKUP(A1,$S$1:$T$1000,2)

or use Insert > Name > define to assign the range S1:T1000 a name, say table, then change formula to

=VLOOKUP(A1,table,2)

Note: If you don't use a 4th argument then lookup range [S1:S1000] needs to be sorted ascending and you may get a "closest match". If you only want exact matches then you should use a 4th argument of zero or FALSE, i.e.

=VLOOKUP(A1,table,2,FALSE)

Using this version you don't need the lookup range to be sorted