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.

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])….