Generate Random List using RANDBETWEEN & FILTER Functions in Excel


Welcome to Excel Jet Consult blog post. In this tutorial, we learn about how to generate random number using the RANDBETWEEN & FILTER Functions in Excel. Let’s get started

Tutorial Video

You can watch the video below for more in-depth explanation. Remeber to subscribe to my YouTube channel for more videos

Sample Data

In the caption below, we have a list of data. We want to use the two functions mentioned above generate random City based on the corresponding Code


RANDBETWEEN Function returns a random number between numbers specified

RANDBETWEEN Syntax:         =RANDBETWEEN(bottom, top)


  • In cell G2 the formula: =RANDBETWEEN(1,5)
  • Click Enter and copy the formula down using auto fill


FILTER Function filter an array or range

FILTER Syntax:           =FILTER(array, include, [if_empty])

  • In cell H2, execute the formula: =FILTER($K$2:$K$6,$J$2:$J$6=G2)
  • Click Enter and copy the formula down using auto fill.

Note, we applied absolute cell reference to the input values for the FILTER array and include arguments respectively. This ensures that when we copy the formula down, the formula doesn’t move relatively! Refer to the video

In the caption below, we can see that we have randomly generated city based on the corresponding code.

See you in the next tutorial. Stay Safe, Stay Alive

Posted in Blog and tagged , , .

Leave a Reply

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