Hello everyone,
Happy New Year!
I am having troubling of getting the formula working in the attached sheet.
I want to get 'Term' defined based on the 'datestart' column, don't know what went wrong...
Can anyone help me sort this out?
I dont think the dates were formatted as dates - i did text to columns on the date columns and your formula worked.
Maybe a slightly easier formula is to use the appoximate match like =INDEX($F$2:$F$5,MATCH(A2,$G$2:$G$5,1)), but what ever works, works!
What result do you want for dates that are not within any of the term periods?
Possibly you can also slightly modify your formula to include the VALUE() function
eg =LOOKUP(2,1/(Sheet1!$G$2:$G$5<=VALUE(Sheet1!A2))/(Sheet1!$H$2:$H$5>=VALUE(Sheet1!A2)),Sheet1!$F$2:$F$5)
Thanks for all the solutions! Very helpful!
