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

Lookup and Reference Examples

  • Basic INDEX MATCH approximate in Excel
  • How to use Excel FORMULATEXT function
  • Count rows that contain specific values in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Lookup and Reference Functions, References and Examples

Data Analysis Examples

  • How to add Trendline to a chart in Excel
  • How to Create Thermometer Chart in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Working With Tables in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation only dates between
  • Excel Data validation date in next 30 days
  • Excel Data validation must not contain
  • Excel Data validation must contain specific text

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:

=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.

Worked Example:   Lookup entire column in Excel

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:   Get column name from index in 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 fill cell ranges with random number from fixed set of options 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
  • Change Case: Uppercase, Lowercase, Propercase in Excel
  • How to count specific words in a range in Excel
  • How to get first word in Excel
  • How to extract text between parentheses in Excel
  • Complete List of Excel Text Functions References and Examples
  • How to calculate future date say 6 months ahead in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Basic Overtime Calculation Formula in Excel
  • Convert Excel time to Unix time in Excel
  • Calculate date overlap in days in Excel
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • RECEIVED function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles