HLookup?

JTL9161

Active Member
Joined
Aug 29, 2012
Messages
363
Office Version
  1. 365
Platform
  1. 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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Forum statistics

Threads
1,143,677
Messages
5,720,260
Members
422,273
Latest member
linds75

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