How to Copy VLookup to Multiple Rows

By Shawn McClain

The Microsoft Excel VLookup function will search for a given value in the first column of a data table and return the value located in a different column, but on the same row, in that table. Once you create your VLookup formula, if you need to copy that formula down to numerous rows, Excel contains a feature called a fill handle that makes the process simple. Before you copy the formula, you'll need to ensure that your cell references are formatted correctly, since standard cell references will change when you copy a formula.

Step 1

Open the Excel 2010 file where you want to copy the VLookup function down to multiple rows.

Step 2

Click on the cell that contains your VLookup formula. Place your cursor into the formula bar located just above the spreadsheet so that you can edit the formula.

Step 3

Place dollar sign symbols in front of any ranges that you don't want to change when you copy your formula down. Excel uses relative references when copying formulas, so if your VLookup function referenced the range A1:D10 and you copy that formula down one cell, the range would change to A2:D11. By placing dollar signs in front of each reference element, such as $A$1:$D$10, you ensure that the reference will not change when you copy the formula. This is especially important when copying VLookup formulas, as the table range should remain constant. Press "Enter" when you are done making changes.

Step 4

Click the cell that contains the VLookup formula. Move your mouse to the small, black box, called a fill handle, in the lower right corner of the cell. Your mouse cursor will change into a plus sign when you are correctly positioned over the box.

Step 5

Click and hold the mouse button on the fill handle. Drag the mouse down the spreadsheet until you reach the last row where you want the VLookup formula to reside. Release the mouse button, and the formula will be instantly copied to all the rows between the original cell and the cell where you released the button.