Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# Discover How to Generate a Random Number in a Table in Excel

Excel allows a user to generate a random number in a table using the INDEX, RANDBETWEEN and ROWS functions. This step by step tutorial will assist all levels of Excel users to discover how to generate a random number in a table in Excel.

Figure 1. The final result of the formula

## Syntax of the INDEX formula

The generic formula for the INDEX function is:

`=INDEX(array, row_num, column_num)`

The parameters of the INDEX function are:

• array – a range of cells where we want to get a data
• row_num – a number of a row in the array for which we want to get a value
• column_num – a column in the array which returns a value.

## Syntax of the RANDBETWEEN Formula

The generic formula for the RANDBETWEEN function is:

`=RANDBETWEEN(bottom, top)`

The parameters of the RANDBETWEEN function are:

• bottom – a value from which we want to get a random value
• top – a value to which we want to get a random value.

## Syntax of the ROWS formula

The generic formula for the ROWS function is:

`=ROWS(array)`

The parameter of the ROWS function is:

• array – a range of cells which rows we want to count.

## Setting up Our Data for the Example

Let’s look at the structure of data that we will use. In the table, we have 3 columns: “Number” (column B), “Product” (column C) and “Price” (column D). In the cell F3, we want to get a random product from the table B3:D9.

Figure 2. Data that we will use in the example

## Generate a Random Value from the Table

We want to get a random product from column C of the table in the cell F3.

The formula in F3 looks like:

`=INDEX(B3:D9, RANDBETWEEN(1, ROWS(B3:D9)), 2)`

The parameter array of the ROWS function is the range B3:D9. The result of the ROWS function is the top parameter of the RANDBETWEEN function, while the bottom is 1. This function returns a random row number of a table. This is the row_num parameter of the INDEX function. The array parameter is B3:D9, while the column_num is 2, as we want to get a value from the second column of the table.

To apply the formula, we need to follow these steps:

• Select cell F3 and click on it
• Insert the formula: `=INDEX(B3:D9,RANDBETWEEN(1,ROWS(B3:D9)),2)`
• Press enter.

Figure 3. Using the formula to generate a random value from a table

The ROWS function returns 7, as our table has 7 rows. Therefore, the RANDBETWEEN returns a random number between 1 and 7, in our case 1. Finally, the INDEX function returns the value from row 1 and column 2 of the table. The result in the cell F3 is “Computer”.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.