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]

[TABLE="width: 177"]
<tbody>[TR]
[TD="colspan: 2"]Sheet 1[/TD]
[/TR]
[TR]
[TD]Study ID[/TD]
[TD]Medication Start[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]2010/12/13[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 378"]
<tbody>[TR]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Study ID[/TD]
[TD]Medical Report Date[/TD]
[TD]Medical report Value[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]2014/10/20[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]2013/12/13[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]2010/12/11[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]


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?