Using VLookups and If Statements Together

[simm5580]

I think I need to combine a Vlookup and IF statement together. Basically, I have a list of participants with a medication start date and I also have a list of medical reports for each of those participants (note: each participant has more than 1 medical report). Essentially, I would like to pull out 1 value from the medical report closest to the start of the medication (before medication start date not after).

 

[gaz_chops]

Post a sample of the data showing cols & rows and what results you expect.

 

[simm5580]

Sheet 1
Study ID	Medication Start
10	2010/12/13

<tbody>
</tbody>

Sheet 2
Study ID	Medical Report Date	Medical report Value
10	2014/10/20	10
10	2013/12/13	20
10	2010/12/11	30

<tbody>
</tbody>

In this case, I would like it to pull out the Medical Report Value "30" since the date is prior to the "Medication Start Date"

 

[etaf]

how is the data structured
can you put a link to a example file on dropbox or onedrive?

 

[simm5580]

Unfortunately not. It is structured across two sheets.

Sheet 1 - Contains Study ID (listed down rows) & medication start Date (1 column)
Sheet 2 - Contains study ID with multiple reports for each study ID (listed down rows), the date for each Medical Report, the Medical Value for each report.

 

[gaz_chops]

You're making this hard! Is there only 1 Study ID? Are the dates sorted? If so Ascending or Descending?