Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Convert text string to valid reference in Excel using Indirect function
  • Two-column Lookup in Excel
  • How to use Excel VLOOKUP Function
  • Extract data with helper column in Excel
  • How to use Excel LOOKUP Function

Data Analysis Examples

  • How To Filter Data in Excel
  • Subtotal function in Excel
  • How to calculate average last N values in a table in Excel
  • Move chart to a separate worksheet in Excel
  • How to sum a total in multiple Excel tables

Data Validation Examples

  • Excel Data validation date in next 30 days
  • Excel Data validation specific characters only
  • Excel Data validation must begin with
  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only

How to get random value from list or table in Excel

by

To get a random value from a table or list in Excel, you can use the INDEX function with help from the RANDBETWEEN and ROWS functions.

Formula

=INDEX(data,RANDBETWEEN(1,ROWS(data)),1)


Note: this formula uses the named range “data” (B5:E104) for readability and convenience. If you don’t want to use a named range, substitute $B$5:$E$104 instead.

Explanation

In the example shown, the formula in G7 is:

Worked Example:   How to count table rows in Excel
=INDEX(data,RANDBETWEEN(1,ROWS(data)),1)

How this formula works

To pull a random value out of a list or table, we’ll need a random row number. For that, we’ll use the RANDBETWEEN function, which generates a random integer between two given values – an upper value and lower value.

For the lower value, we use the number 1, and for the upper value we use the ROWS function to get count the total rows in the table or list:

=RANDBETWEEN(1,ROWS(data))

RANDBETWEEN will return a random number between 1 and the count of rows in the data, and this result is fed into the INDEX function for the rows argument. For the columns argument, we simply use 1, since we want a name from the first column.

Worked Example:   How to create running total in an Excel Table

So, assuming that RANDBETWEEN returns 7 (as in the example) the formula reduces to:

=INDEX(data,7,1)

Which returns the name “Tim Moore”, in row 7 of the table.

Worked Example:   How to generate random number weighted probability in Excel

Note that RANDBETWEEN will recalculate whenever a worksheet is changed or opened.

Post navigation

Previous Post:

How to generate random times at specific intervals in Excel

Next Post:

How to randomly assign data to groups in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check if cell contains some words but not others in Excel
  • How to strip html from text or numbers in Excel
  • How to check if cell contains one of many things in Excel
  • How to count line breaks in cell in Excel worksheet
  • How to remove trailing slash from url in Excel
  • Count birthdays by month in Excel
  • Convert date to Julian format in Excel
  • Get days before a date in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Convert text to date in Excel
  • PRICE function: Description, Usage, Syntax, Examples and Explanation
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
  • YIELDMAT function: Description, Usage, Syntax, Examples and Explanation
  • FV function: Description, Usage, Syntax, Examples and Explanation
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles