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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
7 Methods to Copy VLOOKUP Formula in Excel
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.
Read More: Copy and Paste Formulas from One Workbook to Another in Excel
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 mention, 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.
Read More: How to Copy Formula in Excel without Dragging (10 Ways)
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.
Read More: How to Copy Formula to Entire Column in Excel (7 Ways)
Similar Readings
- VBA to Copy Formula from Cell Above in Excel (10 Methods)
- How to Copy Sheet to Another Workbook with Excel Formulas
- Excel VBA to Copy Formula with Relative Reference (A Detailed Analysis)
- How to Copy a Formula in Excel with Changing Cell References
- How to Copy Formula Down Without Incrementing in Excel
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 know 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.
=VLOOKUP([@Name],Table1,3,FALSE)
- 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.
Read More: Creating and Copying Formula Using Relative References in Excel
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.
=VLOOKUP($F5,$B$5:$D$9,3,FALSE)
- Look at the dataset now.
The inserted formula has been copied to the rest of the cells.
Read More: How to Copy Formula in Excel to Change One Cell Reference Only
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 based has been copied from Cell G5.
In a similar way copy formula to Cell G7. One thing that needs to mention is that 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.
Read More: How to Copy Formula Down with Shortcut in Excel
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.
Read More: How to Copy a Formula in Excel Without Changing Cell References
Conclusion
In this article, we described how to copy the VLOOKUP formula in Excel. One thing needs to keep in mind, VLOOKUP is always vertically, never apply this horizontally. I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.
Related Articles
- How to Copy Array Formula in Excel (3 Easy Methods)
- How to Calculate Data Across Worksheets with Formulas in Excel
- [Fixed] Excel Not Copying Formulas, Only Values (4 Solutions)
- How to Copy SUM Formula in Excel (6 Easy Methods)
- How to Copy Formula to Another Sheet in Excel (4 Ways)
- Making Same Change to Multiple Worksheets (9 Examples)
