Avoiding duplication of names from old chart

BobFast

New Member
Joined
Mar 31, 2010
Messages
12
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,839
Members
413,943
Latest member
Dhornsby21

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top