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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,156
Messages
5,835,703
Members
430,380
Latest member
Psalmysam

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