Tuesday, June 28, 2011

Lock the Table_array in vlookup function

A lot of time, we use vlookup function to help us to retrieve information from another table and we will like to lock the Table_array range. By default, Excel doesn't fix the range and keep shifting it when you copy and paste the vlookup function.

Here's the quick fix.

After you create the 1st vlookup function, it will like this:
VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE)
To lock the table range before you copy and paste the function, add the $ to fix the cell reference, as below:
VLOOKUP(A11,’Product Database’!A$2:D$7,2,FALSE)
This fix the row 2 to row 7. Of course, you can also fix the column A to column D at the same time:
VLOOKUP(A11,’Product Database’!$A$2:$D$7,2,FALSE)