Referencing Multiple Columns in another sheet to Match a Location and Date Range to a Project
So I have been looking through the community for a while and have seen a couple solutions mildly similar to what I am looking to do, but can't quite get it right. Feel free to link me to another solution if you feel it would solve this problem:
I have a reference sheet which I do not own that contains a listing of Tasks with their Start date and Finish date. These tasks fall within larger projects, and there may be multiple Projects planned per Location.
The sheet I am working in tracks the costs incurred on these locations. I will collect the information of the Location name and the date of the cost in my sheet. I am looking to use a formula to match the Location and Date to look up which project was active on that Location at that time.
For example: If the reference sheet shows that Location A has Task A1A for Project 1 Start 1/1/20, Finish 2/1/20. Then has Task A1B for Project 1 Start 2/2/20, Finish 3/1/20.
There is also Project 2 on Location A with Task A2A Start 6/1/20, Finish 8/1/20.
In the Cost record there will be entries for Location A on 1/15/20, 5/1/20, and 7/1/20. I want the column in this cost sheet to display Project 1 for the 1/15 cost, Project 2 for the 7/1 cost, and "Inactive" for the 5/1 cost. by looking for the cost entry to be on the same location, between the start and end date of one of the tasks in that project.
Any advice would be much appreciated! I will have to build a separate sheet if the description is not enough to explain this situation due to the format of the reference sheet. please let me know if this is necessary.