# Avoiding duplication of names from old chart

#### BobFast

##### New Member
I have a golf chart from last year with 12 rows and 4 columns. (12 teams of 4 players each)
We pick new teams every year and you cannot play with a player this year that you played with last year.
I have a rather long, IF/OR/LOOKUP formula and conditional formatting that turns a cell red if a match (YES) is found in last year’s chart. It seems clumsy and long to me and I’d like to find a better way… and learn something new.

Columns A, B, C & D each have 12 names. I have these formulas in columns E, F & G.

=IF(OR(VLOOKUP(\$A12,LYTmLkUp,2)=B12,VLOOKUP(\$A12, LYTmLkUp,3)=B12,VLOOKUP(\$A12,LYTmLkUp,4)=B12),"YES","NO")

=IF(OR(VLOOKUP(\$B12,LYTmLkUp,2)=C12,VLOOKUP(\$B12, LYTmLkUp,3)=C12,VLOOKUP(\$B12,LYTmLkUp,4)=C12,VLOOKUP(\$A12,LYTmLkUp,2)=C12,VLOOKUP(\$A12,LYTmLkUp,3)=C12,VLOOKUP(\$A12,LYTmLkUp,4)=C12),"YES","NO")

=IF(OR(VLOOKUP(\$B12,LYTmLkUp,2)=D12,VLOOKUP(\$B12, LYTmLkUp,3)=D12,VLOOKUP(\$B12,LYTmLkUp,4)=D12,VLOOKUP(\$A12,LYTmLkUp,2)=D12,VLOOKUP(\$A12,LYTmLkUp,3)=D12,VLOOKUP(\$A12,LYTmLkUp,4)=D12,VLOOKUP(\$C12,LYTmLkUp,2)=D12,VLOOKUP(\$C12,LYTmLkUp,3)=D12,VLOOKUP(\$C12,LYTmLkUp,4)=D12),"YES","NO")

LYTmLkUp is a named range of 4 columns and 48 rows. The original chart is only 4 X 12 but I needed to make it 48 rows so that the name of each golfer appeared in the first lookup column. There should be a better way.
The formula in column E uses the entry in column B to search for a match with the A golfer
The formula in E uses the entry in column C to search for a match with the A & B golfer.
The formula in F uses the entry in column D to search for a match with the A, B & C golfer
Can someone improve on this?
Thanks Much.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Replies
4
Views
318
Replies
6
Views
280
Replies
6
Views
236
Replies
7
Views
413
Legacy 143009
L
Replies
4
Views
279

1,218,593
Messages
6,143,379
Members
450,483
Latest member
santvik234

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back