=RND()*($F$15)+A$1

F15 is the total number of employees
F16 is the number of open positions

F15 is determined by subtracting the number of open positions (F16) from the
total number of positions (150)

F15 = 150-F16
F16 = COUNTIF(B1:B150,"")

"John C" wrote:

My formula given should give you last name, first name. If you are getting
the same person every time, perhaps you should share with us how you are
generating the random number?
--
** John C **

"REJesser" wrote:

Thanks for your help. And now for a new challenge...

Your suggestion worked out great except when more than one individual has
the same last name. When that takes place, the first person with that last
name is identified.

Ex.
1. Smith, Alvin
2. Smith, Brian
3. Smith, Cory

Even when the #3 is randomly generated, Alvin Smith is still identified.



"Sheeloo" wrote:

Use
=VLOOKUP(D1,A1:B150,2).

i.e. change A150 to B150

"REJesser" wrote:

I am attempting to create a Random Drug Screen Identifier. I can handle the
whole random number creation. What I haven't been able to figure out is how
can I have the name associated with the number identified. For example, I
have in the first column, the number 1. Each cell below counts up...=A1+1;
=A2+1, etc. The next column (B) is devoted to the last names of the
employees. The next is employee first names (C). Column D generates 10
random numbers from 1 to the total current number of employees. I can
certainly print out the list of employees and, by hand, highlight those
numbers which were generated. I would prefer the worksheet do that for me.
I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that
this would actually list the name identified with the first random number.
The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'.
Any help would be greatly appreciated.
Thanks.