Vlookup and RandBetween Function

%3CLINGO-SUB%20id%3D%22lingo-sub-1825431%22%20slang%3D%22en-US%22%3EVlookup%20and%20RandBetween%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1825431%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20generate%20a%20list%20of%206%20unique%20names%20from%20a%20list%20of%20names%20with%20a%20number%20value%20assigned%20to%20them.%26nbsp%3B%20However%2C%20When%20doing%20the%20below%20function%20I%20am%20getting%20multiples%20of%20the%20same%20name.%20I.E%20%3A%20John%2C%20John%2C%20David%2C%20Smith%2C%20Dustin%2C%20Dustin.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(RANDBETWEEN(1%2C%24J%242)%2C%24A%3AD%2C4%2CTRUE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20I%20can%20use%20this%20code%20to%20pick%20a%20random%20name%20from%20my%20list%20and%20the%20second%20name%20be%20a%20random%20name%20from%20the%20same%20list%20and%20if%20it%20selects%20a%20name%20that%20has%20been%20used%20before%20it%20selects%20a%20different%20name%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20would%20be%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1825431%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1825523%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20and%20RandBetween%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1825523%22%20slang%3D%22en-US%22%3EAre%20you%20able%20to%20put%20the%20random%20number%20next%20to%20each%20name%20in%20the%20list%3F%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20could%20then%20pull%20that%20data%20into%20Pivot%20Table%20and%20do%20a%20Top%205%20%3CBR%20%2F%3E%3CBR%20%2F%3EOr%20if%20you%20have%20Excel%20365%20you%20could%20use%20the%20SORTBY%20function%20to%20return%20a%20sorted%20list%20and%20then%20reference%20the%20first%205%20rows%20of%20that%20in%20another%20formula.%20%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20have%20365%20I'll%20attach%20an%20example%20later.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello All,

 

I am trying to generate a list of 6 unique names from a list of names with a number value assigned to them.  However, When doing the below function I am getting multiples of the same name. I.E : John, John, David, Smith, Dustin, Dustin.

 

=VLOOKUP(RANDBETWEEN(1,$J$2),$A:D,4,TRUE)

 

Is there any way I can use this code to pick a random name from my list and the second name be a random name from the same list and if it selects a name that has been used before it selects a different name?

 

This would be helpful.

 

3 Replies
Highlighted
Are you able to put the random number next to each name in the list?

You could then pull that data into Pivot Table and do a Top 5

Or if you have Excel 365 you could use the SORTBY function to return a sorted list and then reference the first 5 rows of that in another formula.

If you have 365 I'll attach an example later.
Highlighted

@Wyn Hopkins The names are associated with a group of numbers such as name 1 is any number from 1-71 and there are several names with several different numbers values for each name,  Which makes this a little harder.  I want the second name to take a random number and if it generates the same as the first name it selects another random number to get a different name.

Highlighted
Any chance you can upload a sample file with an example?