Need a table based on DATE... but its complicated

[indiemusicboy]

I'm trying to create table or something dependent if the (renewal) date is upcoming. If that date is 3 months from today, it will list a series of information related to that date. So it ranks based on date but has to pull a lot of criteria across the worksheet associated to that date. Here is the criteria:

1. Client name (single reference) [F3]​

2. Service (multiple references) [B138:B145]​

3. Renewal date (multiple references) [P138:P145]​

4. Value (multiple references) [O138:O145]​

5. The name of the account rep (single reference) [L3]​

6. Health score (multiple references) [L138:L145]​

7. CSAT (single references) [R10]​

All of the above data points are captured in a workbook in which each tab is a collection of data for a specific customer. Those tabs are based on a template so each customer tab is formatted the exact same way/same cell locations. In other words, the "client name" is always going to be in F3. The services are always going to be in a range B138:B145. I noted those ranges/cells above in between the "[ ]"

I've created "Index" worksheet that pulls all the data per worksheet into an index view so I could create pivot tables.

My problem is that each client could have 1 service [B138] or multiple services [B138:B145]. Some of those services may have a renewal date coming up. Some may not. I only care for the series of data above IF the date is 3 months from TODAY.

Images are below to help you understand the layout a bit more...

VIEW OF TABS: I have a rollup or "dashboard" tab that summarizes key data points I want to pull from those subsequent client tabs. [image below]

VIEW OF SUMMARY: Example of the field I want populated in the "Dashboard" tab ranked based on data per customer worksheet. [image below]

VIEW OF CUSTOMER WORKSHEET SOURCE DATA: The data it is extracting from looks like this on each customer tab. [image below]

VIEW OF "INDEX" WORKSHEET:

 

[indiemusicboy]

Happy to explain further if it helps clarify the ask. I understand that I'm not the best at relaying the flow at times.

 

[indiemusicboy]

I wonder if I can somehow create a formula in column Q that lists dates within 90 days of TODAY from the INDEX worksheet - which will populate dates in Q52 thru Q67 (one each) - I can then use OFFSET to then grab the corresponding data like Customer name, service, etc. from cells in that same row within INDEX too?

So Q52 populates a date if it finds one that is within 3-months-from-today (from INDEX), and then I use OFFSET to pull its corresponding data from that date's row (from INDEX), to populate matching data for that date's related service, value, CSM, Health, etc?

-or-

Is it more like using an INDEX+MATCH function to do all at the same time?