How do I mix autofill and vlookup in Excel?

Question

Whenever I use the autofill square in the bottom right corner, it makes the looked-up cell (the first value) move down from, say C3 to C4, which is good, but it also takes the range from, say, C3:D303 to C4:D404, which is actually kinda bad. Is there any way to stop this from happening? I cannot type all of the formulas out by hand, as I need 400 of the sort.

Answer 1

If you want a cell reference to remain static, i.e. not change, put a $ before the column and/or row reference.  For example,  $A$1 will not change during autofill.  If the reference was $A4 the 4 would change during autofill.  Change your references to this format.

Answer 2

Select the lookup table data and name it (select the cells, click into the name box at the left side of the formula bar and type a unique name with no spaces). Use the name in the lookup function instead of the range.