How to Copy VLOOKUP Formula in Excel (7 Easy Methods)

VLOOKUP stands for vertical lookup. When we need to search for any value from a table or range based on a reference in Excel, use this VLOOKUP function. There are also two other lookup functions available. Those are: the HLOOKUP function works horizontally, and the XLOOKUP function works in both directions. To copy any formula based on those lookup functions is very complicated. In this article, we will discuss how to copy the VLOOKUP formula in Excel with proper illustrations.

How to Copy VLOOKUP Formula in Excel: 7 Methods

In this article, we will apply 7 different methods to copy the formula based on the VLOOKUP function. We will consider the following dataset. Here, we applied a VLOOKUP formula on Cell G5.

Now, we will show how to copy this formula in Excel.

1. Simple Copy-Paste Method to Copy VLOOKUP Formula

In this section, we will use the simple copy-paste method to copy the VLOOKUP formula in Excel.

📌 Steps:

• Move to Cell G5.
• Click on the Copy option from the Clipboard group from the Home tab.

• Go to the next Cell G6.
• Click on the Paste option from the Clipboard group.

• Look at the dataset.

We can see VLOOKUP formula of the corresponding Cell G6 has been pasted.

• Similarly, copy the formula on Cell G7.

This is another alternative way to copy formulas using a keyboard shortcut.

Select Cell G5 and press Ctrl+C. Then, go to Cell G6 and press Ctrl+ V. Similarly, copy the formula to the rest of the cells.

2. Copy VLOOKUP Formula Down a Column by Dragging

In this section, we will drag the Fill Handle icon to copy the VLOOKUP formula to the required cell.

📌 Steps:

• Place the course at the right bottom corner of Cell G5.

We can see a plus symbol (+) there.

• Now, drag this plus symbol downwards till Cell G7.

We can see the formula copied, and the result is showing.

But one thing needs to be mentioned, we may not need to copy the format of the base cell. Follow the next step for this.

• Click on the down arrow at the right-bottom of Cell G7.

• Choose the Fill Without Formatting option.

We can see the formatting has been removed from the copied cells.

3. Copy VLOOKUP Formula to Entire Column

The method is also based on the Fill Handle feature of Excel. Dragging the Fill Handle icon is not a solution when we have to copy a formula to a huge number of cells or through the entire column. Use the below method in that case.

📌 Steps:

• We can see the Fill Handle icon at the right-bottom side of Cell G5.

• Then, double-click on the Fill Handle icon.

Look at the dataset now. The VLOOKUP formula has been copied through Column G. Like method 2, we can also remove the formatting from the copied cells.

4. Create Excel Table to Copy VLOOKUP Formula

The table is one of the most powerful features of Excel. It provides a wide range of features in a package to the selected cells to form a table. Have a look at the below section to learn how to use the table to copy the VLOOKUP formula in Excel.

📌 Steps:

• First, we will form a table. Select the Range B5:D9.
• Then, press Ctrl + T.
• The Create Table appears with the selected range.
• Put a tick mark on My table has headers option.
• Finally, press the OK button.

• We can see a table has been formed.

• Create another table selecting the Range F5:G7.

• Now, go to Cell G5 and pout the following formula.

• Then, press the Enter button.

We can see the inserted formula has been copied to all cells of the Salary column of the table. This is because the table performs a similar task throughout the cells of the column.

5. Insert VLOOKUP Formula in One Cell and Copy to Multiple Cells with a Single Button Press

In this section, we will insert the VLOOKUP formula on one cell of the selected cell, and that will copy to the rest of the cells.

📌 Steps:

• First, select Range G5:G9.
• Press the F2 button to go to the editable mode.

• Now, insert the following formula.
• Then, press the Ctrl + Enter buttons.

• Look at the dataset now.

The inserted formula has been copied to the rest of the cells.

6. Apply a Keyboard Shortcut to Copy VLOOKUP Formula in Excel

This is the simplest way to copy the VLOOKUP formula. Just use a keyboard shortcut only. Cell G5 contains a VLOOKUP formula. We need to copy this formula on Cell G6.

📌 Steps:

• Place the cursor on Cell G6.
• Press Ctrl + D from the keyboard.

We can see the respective formula of Cell G6 has been copied from Cell G5.

In a similar way copy formula to Cell G7. One thing that needs to be mentioned is that the formula will copy only to the next adjacent cell that contains a formula. If the adjacent cell does not contain a formula, this method will not work.

7. Replicate VLOOKUP Formula Without Changing Cell Reference

In this section, we will copy the VLOOKUP formula without any change. In the previous methods, our copied VLOOKUP formula changed concerning cell reference. We can do this in two simple ways. One is a keyboard shortcut and the other one is the use of absolute cell reference in the formula.

Keyboard Shortcut:

• We go to the adjacent cell of Cell G5.
• Then, press Ctrl + ‘ (apostrophe) and the Enter button.

We can see the formula has been copied to Cell G6.

Absolute Cell Reference:

• Another way, we will apply absolute cell reference in the formula. Look at the formula of Cell G5.
• We used the following formula based on the absolute cell reference.
• Now, copy and paste this formula following method 1 on Cell G6.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we described how to copy the VLOOKUP formula in Excel. One thing needs to be kept in mind, VLOOKUP is always vertically, never apply this horizontally. I hope this will satisfy your needs.

Related Articles

• 10 Best Practices with VLOOKUP in Excel
• 7 Practical Examples of VLOOKUP Function in Excel
• VLOOKUP Example Between Two Sheets in Excel
• How to Use Dynamic VLOOKUP in Excel
• Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
• How to Make VLOOKUP Case Sensitive in Excel
• VLOOKUP from Another Sheet in Excel
• How to Use VLOOKUP Formula in Excel with Multiple Sheets
• How to Remove Vlookup Formula in Excel
• How to Apply VLOOKUP to Return Blank Instead of 0 or NA
• How to Hide VLOOKUP Source Data in Excel

<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel