JTL9161
Well-known Member
- Joined
- Aug 29, 2012
- Messages
- 591
- Office Version
- 365
- Platform
- Windows
I have a league golf schedule. 12 names in 12 columns with each weeks opponent listed in each row under each name. I have a date range in column A & B so I use the formula: (B129) is the today's date.
=LOOKUP(2,1/(Schedule!$A$2:$A$19<=$B$129)/(Schedule!$B$2:$B$19>=$B$129),Schedule!$C$2:$C$19)
For Example "BILL" is in C1. His week 1 opponent is in C2 "BOB", week 2 in C3...etc. I copy the above formula down 6 times for each golfer to give me the following:
Bill vs. Bobl
aaa vs. xxx
bbb vs zzz
ccc vs yyy
ddd vs vvv
eee vs ppp
The Bill name is hard coded and the formula looks in Bill's column of opponents (Col C, at end of formula) based on the date range and returns that opponent (Bob)
The problem with my formula that I have for each member's column in the schedule is that since Bill is the next listed golfer I get this:
Bill vs. Bob (then I get)
Bob vs. Bill
aaa vs. xxx
bbb vs zzz
ccc vs yyy
ddd vs vvv
I lose the eee vs. ppp match because its moved down to the 7th row because Bill vs Bob is duplicated.
How can I change the end of formula to look for golfers name (Bob, HLookup?) instead of looking at his specific column (C)? This way I can hard code six of the names down and use the formula to search for each hard coded name and choose the name down that column with the date range to give me his opponent.
Hope you can understand my question.
Thank you in advance.
James
=LOOKUP(2,1/(Schedule!$A$2:$A$19<=$B$129)/(Schedule!$B$2:$B$19>=$B$129),Schedule!$C$2:$C$19)
For Example "BILL" is in C1. His week 1 opponent is in C2 "BOB", week 2 in C3...etc. I copy the above formula down 6 times for each golfer to give me the following:
Bill vs. Bobl
aaa vs. xxx
bbb vs zzz
ccc vs yyy
ddd vs vvv
eee vs ppp
The Bill name is hard coded and the formula looks in Bill's column of opponents (Col C, at end of formula) based on the date range and returns that opponent (Bob)
The problem with my formula that I have for each member's column in the schedule is that since Bill is the next listed golfer I get this:
Bill vs. Bob (then I get)
Bob vs. Bill
aaa vs. xxx
bbb vs zzz
ccc vs yyy
ddd vs vvv
I lose the eee vs. ppp match because its moved down to the 7th row because Bill vs Bob is duplicated.
How can I change the end of formula to look for golfers name (Bob, HLookup?) instead of looking at his specific column (C)? This way I can hard code six of the names down and use the formula to search for each hard coded name and choose the name down that column with the date range to give me his opponent.
Hope you can understand my question.
Thank you in advance.
James