Selecting a Set of Random Numbers, Avoiding Duplicates

In this article, we will learn how and where we can generate random numbers without repetition in Microsoft Excel.

We need the random numbers when we want to choose the lottery numbers. We need lottery numbers at so many places for admission, savings, etc.

To return the random numbers, avoiding duplicates, we will use Rand, Rank and VLOOKUP function along with row function. We use Rand function to select a set of 5 random numbers between 1 and 10, without getting duplicates.

Let’s take an example to understand.

We want to return lucky lottery numbers. So, to generate the lucky numbers, firstly, we will generate random numbers by using Rand function.

In data range A1:C11, Step 1:-

  • Enter Rand function in Column A :- = RAND()


image 1


Note: - When we will do any activity in the sheet, every time rand function will change the random numbers.

Now, we will return the rank to every random number’s by using the Rank function.

  • Enter the following formula in Column B:- =RANK(A2,$A$2:$A$10)


image 2



  • Enter the numbers 1-10 in column C.


image 3


  • In lottery range (A14:B19), in column B, use the VLOOKUP and ROW functions in the following formula:-
  • =VLOOKUP((ROW()-ROW($A$19)+1),$B$2:$C$12,2,FALSE)


image 4


As a result, 5 unique random numbers from 1 to 10 will be displayed in Column B. The numbers will change with each recalculation.

This is the way we can select a set of random numbers avoiding duplicates by using the formulae in Microsoft Excel.



image 29

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at



  1. Please show how to create a Random Number Genertor for 10 columns each labeled for a month with 350 rows. The result is create a number that will be associated to another spreadsheet grid number equated to an inventory storage location. The intent is to do 10% invtory per month without duplication of inventory locations.

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.