Try-it! Transcript



How to use absolute cell references to copy a formula down a column

Copy VLOOKUP

1. Select the first cell in the column where you want to copy the formula.

2. Enter your formula, but place dollar signs in front of each column letter:

= VLOOKUP ($B3,$B3:$F25,3,FALSE)

1. Press Enter to commit the formula.

2. Grab the fill handle around the formula and drag it down the column.

Want more?

VLOOKUP function

Quick reference card: VLOOKUP refresher

Quick reference card: VLOOKUP troubleshooting tips

Whenever you need to copy a VLOOKUP formula, or any other formula, you have to use absolute cell references.

For example, it would be nice to list all the Page Names next to their Page Numbers and Hit data, instead of just looking them up one at a time.

To do that, we'll need to copy the formula down this column.

Whenever you copy a formula, you have to use absolute cell references, and you do that by adding dollar signs to your arguments.

So, I'll demonstrate by adding a heading, and then I'll start the formula.

But this time, instead of entering a blank cell as my first argument - my lookup value – I'll enter $A4.

The dollar sign is what makes it an absolute cell reference. In this case, it forces VLOOKUP to use just the values in column A.

I am using those because I know I want to match each Page Number with its Page Name, so I'll use those values directly instead of the empty placeholder cells that you have seen in the previous videos.

Next, I enter the name of the other worksheet, an exclamation point, then in my cell range, I also use two more dollar signs, in front of the A and the B, like so.

I'll enter the same column reference argument, 2, and FALSE as my range lookup argument, again because I want exact matches.

Press Enter, and the formula returns "Home Page".

Now, grab the fill handle, drag it down the column, and there you go. All the data, easy-to-scan.

One thing to remember, though: I only used dollar signs in front of the column letters.

If I separate column letters and row numbers with dollar signs, like this, VLOOKUP only returns one value, the first, one.

That happens because dollar signs prevent Excel from changing column and row values in a formula when you copy that formula.

Now, here's something that can help. If you are having trouble adding dollar signs to a formula, put your cursor in an argument and press F4.

As you press the button, it inserts dollar signs in each allowable place in the argument.

In other words, it won't let you put one in the wrong place.

Just press F4 until the dollar sign is in the right place, and go on to your next argument.

So that's a final gotcha, and while we are talking about them, let's recap the other possible mistakes: Your lookup table has to be to the right of your lookup values, or on another worksheet.

You have to arrange your data in columns, and enter FALSE when you want an exact match, and TRUE (or nothing) if you want a partial match.

Finally, if you are going to copy the formula, make sure you put the dollar signs in the right places.

And to learn more about VLOOKUP, see the links in the course summary.