Checking for duplicates between two different sheets

Question

We are setting up a raffle where we will send out a form for someone to completed to enter the raffle.  Once submitted we will take the (Full Name) column and randomly select a winner. We do have a few things on this sheet, we check to make sure there are no duplicates on the sheet, and once a winner is selected and checked off on the sheet, it will copy that line to another sheet called Winner list.

This is a template, so next raffle comes up we use the same type of sheet and form, but we do not want the same person to win more than once per year.

What I am looking for is a formula or something to look at the sheet Ticket Tuesday, reference the full name column, then look at the Winner list sheet reference the same full name column and see if there are duplicates.  If there are I would like it to Flag that line under the column Past Winner on the Ticket Tuesday sheet.

Thanks for any help you can provide.

darkmatter · Answer

Hey @Ape_Man819

You could use an INDEX MATCH to run this for you.

* Set up past winner sheet* Enter all winner names in Column A
* Enter text in column B for every row, 'Winner'

* Set up sheet reference* In Grid View, open up Sheet Reference Manager.
* Select the past winners sheet as a source.
* Create a reference for column A. Call it 'Name check'.
* Create a reference for the column B. Call it 'Winner check'.

* Update live sheet* Insert a 'checker' column in the live sheet
* insert column formula: 
* =INDEX({Winner check}, MATCH([Name]@row, {Name check}, "Not a winner"))

Let me know how you get on.

Matt

Philip Robbins · Answer

@Ape_Man819 Something along the lines of =IF(MATCH([Full Name]@row, {Winner List_Full Name}, 0)>0,"Past Winner","") should work.