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.
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.