=VLOOKUP(A2,$B1:D$90,2,FALSE)

You may have seen some formulas in another person’s workbook with funny symbols that look like this, $A:$V or $A1:B1. The ‘dollar-sign’ in this case locks the cell reference, and it’s placement is important. 

You can either add the symbols manually using the SHIFT+4 key on your keyboard, or using the function key F4 on your keyboard. Hitting F4 multiple times will toggle through the locking scenarios. 

For the example of using columns, locking the cell reference is simple –  either ‘Off’ (A:V) or ‘On’ ($A:$V). But for specific ranges (A1:B1), you have a few ways of locking this reference to choose from. For each Cell, you can lock either the Row, or the Column. 

Meaning, $A$1 locks the reference to A1, and no amount of dragging either vertically or horizontally will change that. However, $A1 will only lock the column on “A”, and A$1 will only lock the row on “1”. 

Let’s explore this a little further to ensure it makes sense…

Rundown of the Locking Scenarios

Locking neither the Row or Column: (A1)
If you were to drag a formula with the reference ‘A1’ in it horizontally, the column would change (A1, B1, C1, D1…), if you were to drag it vertically, the row would change (A1, A2, A3, A4, A5…)

Locking both the Row and Column: ($A$1)
If you were to drag a formula with $A$1 in it either horizontally or vertically, the reference in that formula would not change, and always be $A$1. 

Locking the Row and NOT the Column: (A$1)
If you were to drag a formula with A$1 in it horizontally, the column would change(B$1, C$1, D$1…), if you dragged it vertically, nothing would change (A$1). 

Locking the Column and NOT the Row: ($A1)
If you were drag a formula with $A1 in it horizontally, nothing would change ($A1), if you dragged it vertically, the row would change ($A1, $A2, $A3…)

Advertisements