This post will guide you through two different methods to** repeat cell values N times in Excel**, including using the VLOOKUP function, and VBA code.

Table of Contents

## 1. Repeat Cell Value N Times using VLOOKUP Function

Assuming that you have a list of data that contain two column and one column is cell value that you want to repeat, and another column contain N number. To repeat cell value N times in Excel, just do the following steps:

**Step1:** Insert a column to the left of A, so your current A and B columns are now B and C.

**Step2:** Put **1** in Cell **A1**.

**Step3:** Put** =A1+C1** in **A2 **and drag the AutoFill Handle down to Cell A5.

**Step4:** Put an empty string in B5, by just entering a single quote (‘) in the cell

**Step5:** Put a number **1** in E1, a number 2 in E2, and copy down as to get **1, 2, …, 14**

**Step6:** Type the following formula based on the VLOOKUP function into the formula box of cell F1 and then drag the AutoFill Handle over other cells to apply this formula.

`=VLOOKUP(E1,$A$1:$B$5,2)`

## 2. Repeat Cell Value N Times with VBA Code

You can also use the VBA Code to select a range of cells, then select the destination cell and repeat N time for the selected range of cells based on the next cell in the same row. Just do the following steps:

**Step1:** Press **ALT+F11** to open the **Visual Basic Editor**.

**Step2:** Click on **Insert** > **Module**.

**Step3:** Paste the following code into the module.

**Step4:** Press **F5** to execute the code. Or press ALT+F8 to open the Macro dialog box. And select the Macro **RepeatCellValuesNTimes_excelhow** from the Macro list. Click **Run** button.

**Step5:** Select the range of cells that you want to repeat the value in, and click **OK**.

**Step6:** Select the destination cell where you want to start repeating the value, and click **OK**.

**Step7:** The value will be repeated N times in separate cells.

## 3. Video: Repeat Cell Value N Times in Excel

This video will show you how to repeat cell value N times in Excel using both the VLOOKUP formula and VBA code.

## 4. Conclusion

These methods have its own benefits and drawbacks, so it’s important to choose the one that best fits your needs. Through those two methods, you’ll be able to easily repeat any cell value N times in Excel.

## 5. Related Functions

- Excel VLOOKUP function

The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….

## Leave a Reply

You must be logged in to post a comment.