SampleStatus.xlsx

Ok I have one spreadsheet with a calender view of all our clients and service dates. I want to search other spreadsheet by service date and return the STATUS so that I can easily view it on my calender view spreadsheet.


Second spreadsheet
ID+Start Date Possible Middle Dates ID+End Date MEMBER ID MEMBER NAME SERVICE START DATE SERVICE END DATE Payment Amount STATUS

My second spreadsheet original had the member id, member name, the server start date, end date, payment amount, and current status.
I concatenated their Member ID and Service Dates in the ID+Start Date and ID+End Date columns so that I can search each unique member's service dates (as the service dates for members overlap)

Originally I was trying to figure out how to search for any range between the service start date and end date. But then I realized if I did that I would have to check the payment amount to see if the number of service dates matches amount of payment (because what if we did provided services on Monday and Thursday only?, Tuesday and Wednesday would get mistakenly marked too. Checking for date ranges and payment amounts was beyond my skills so I created a column for middle dates (many of our services occur in 3 day blocks.) But I still don't know how to check all three columns. I was using Vlookup but it only checks the start date column. any ideas?