Random ticket number for raffle

[Matt]

If I had 1000 people who have paid their money to enter a raffle, I would
like to be able to assign a ticket number to each person without having to
write each person's name on each ticket (and ticket stub). I have read
through all Q+As without any luck. Examples of tickets are as follows: Blue
A1, Yellow C32, Red E86 etc. Each ticket book has a maximum of 100 tickets
(eg Blue A1 - Blue A100).

I was intending on using sheet 2 as the entry sheet in which to enter ticket
information ticket colours in column A, ticket letters in Column B, ticket
numbers in Column C and then concatenate them in Column D. On sheet 1, I was
going to enter all peoples names into Column A and have a formula in Column B
that references Column D on sheet 2 and selects a ticket number at random.

What formula should I use to return a randomly selected ticket number?
Thanks in advance.

 

[Chris Bode]

Well, I suggest you to use vlookup because it is a convenient way to
make a reference between sheets…even workbooks
1.select a cell in column (say B1) in sheet1 and enter followin
formula
=VLOOKUP(A1,Sheet2!A13,4,FALSE)



Chris

 

[Matt]

Thanks Chris but I don't think VLOOKUP helps. I was thinking something like:
=INDEX(Sheet 2!A$2$6,RANDBETWEEN(1,COUNTA(A$2$6)))
This formula returns a random value but the values are repeated.
Assuming Row 1 contains headings (on both Sheet1 and Sheet2):
Sheet2!D2 to Sheet2!D6 contain concatenated values that look like this:
Red A1
Red A2
Red A3
Red A4
Red A5
Sheet1!A2 to Sheet1!A6 contain names as follows:
John
Jane
Frank
Mary
George
What I am looking for is a formula to put into cells Sheet1!B2 to Sheet1!B6.
Formula needs to randomly select a ticket number from cells Sheet2!D2 to
Sheet2!D6. Value needs to be unique and cannot be repeated (error message
needs to be displayed maybe if not enough tickets?)