I'm restricted to office 2016 so some functions in newer versions of excel are unavailable to me. I have a sheet the contains many data points. The ones relevant to my problem are start date, end date (both formatted as date), group number, location (as a header) and capacity. I thought breaking this down into smaller chunks would make this easier but I'm not having any luck.
First chunk, in my mind, is to figure out what group number we are in based on today's date. Determining today's date is easy and since I don't need the date to remain static I just use the TODAY() function in a cell. Now I'm running into a format problem with my data that, for certain reasons, I'm not allowed to rearrange. See the Group numbers are stored to the left of the start dates and end dates, respectively. If their positions were reversed I'd just do a VLOOKUP and call it good. I've tried integrating the MATCH function but my understanding of that is limited. Here's what I've tried thus far:
C63 is the current date, B column contains start date, E column contains end date, A column contains group number.
=VLOOKUP(C63, CHOOSE({1,2}, B2:B54, A2:A54), 2, FALSE)
Alternatively I've tried the below which also checks against both start and end dates
=LOOKUP(2, 1/(B2:B54<=C63)/(E2:E54>=C63), A2:A54)
Once I have this figured out I should be able to do a simple reference based on group number since each group number row contains the location capacity already sorted by location. Thank you for your help
EDIT
Realized I was asking the wrong question in the problem. Firstly, the whole falling between 2 dates function is too problematic since multiple classes can be running concurrently, so searching based on today's date, for example, would return up to 3 values. So I had to restrict the lookup based on start date only which I used data validation in a drop down list to accomplish. Then I used 2 variations of the same formula to figure out the group ID in one location and the capacity in another.
To determine the Group ID based off of start date:
=INDEX(A2:A54, MATCH(C63, B2:B54,0))
//A Column is group ID, C63 is the start date, B Column is all available start dates
To determine the capacity based off of group ID:
=INDEX(H2:H54, MATCH(C64, A2:A54,0))
//H Column is capacity, C64 is the group ID that I previously determined above, A Column is all group ID's
CodePudding user response：
Realized I was asking the wrong question in the problem. Firstly, the whole falling between 2 dates function is too problematic since multiple classes can be running concurrently, so searching based on today's date, for example, would return up to 3 values. So I had to restrict the lookup based on start date only which I used data validation in a drop down list to accomplish. Then I used 2 variations of the same formula to figure out the group ID in one location and the capacity in another.
To determine the Group ID based off of start date:
=INDEX(A2:A54, MATCH(C63, B2:B54,0))
//A Column is group ID, C63 is the start date, B Column is all available start dates
To determine the capacity based off of group ID:
=INDEX(H2:H54, MATCH(C64, A2:A54,0))
//H Column is capacity, C64 is the group ID that I previously determined above, A Column is all group ID's
Huge thanks to Solar Mike for helping me get to this solution!