Using information from two tables to create a new table

Hi,

I have a table of information which gives participant ID, drug start date, drug stop date

Please Login or Register  to view this content.

I have another table which gives participant ID and scores.

Please Login or Register  to view this content.

I would like a table which includes
Before score (or scores)= any score which comes before the start date
During score (or scores) = any score which comes after the start date BUT before the stop date (so scores which are collected when someone is taking the drug).

(Note that each participant may have more than one before or during score).

Please Login or Register  to view this content.

Any help with this would be very much appreciated!

Thanks!

Hello London123 and Welcome to Excel Forum.
This proposed solution employs a helper range which is highlighted in yellow on sheet 2, and which may be hidden for aesthetic purposes.
The formula that populates the helper range is:

Formula:

Please Login or Register  to view this content.

The formula that populates the table on sheet 3 is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

If possible upload a desensitized version of the file that 'doesn't work' and we'll attempt to figure out the problem.
To upload a file click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window. Note: The paperclip icon doesn't work.
Let us know if you have any questions.

The issue with the formula not working at all is that on Sheet1 in cells B1 and C1 there are spaces after the terms 'Start date' and 'Stop date'. There is a further issue on sheet 2 in some of the rows, such as 5, that was due to spaces in cells C5 and D5. I believe that adding the trim function to the first argument of the formula has corrected for that.
Let us know if you have any questions.

The table names were switched. It should work fine now.
Let us know if you have any questions.