Indexing or looking up with two different criteria for multiple matches

[cagriyuksel]

Hi all,

I have two different measures (measure 1, measure 2), in two different spreadsheets (sheet 1, sheet2), with different values.
These measures are carried out for each study participant (David, Maria and Bob) several times on different dates. I'm trying to bring together the dates of the two measures for each participant if these measures are done within 30 days (before or after) of each other, or those of the two measures with the closest dates, for each participant. (example pasted below)

I tried to tackle this using index, match, if and day functions in spreadsheet 2, in a separate column: =INDEX(Sheet2!B:B,MATCH(Sheet2!A2, IF(OR(DAY(Sheet2!B:B-Sheet1!B:B)<30, DAY(Sheet2!B:B Sheet1!B:B)>-30), Sheet2!A:A),0))

but this doesn't seem to work. Can anybody please help me with this? I'm pasting the example below.

Thank you!

Cagri

Sheet1:
[TABLE="width: 287"]
<tbody>[TR]
[TD]Subject[/TD]
[TD] Measure1 Date[/TD]
[TD] Value[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD="align: right"]3/27/2015[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD="align: right"]2/28/2016[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD="align: right"]1/15/2014[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD="align: right"]11/18/2016[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]3/5/2017[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]3/6/2018[/TD]
[TD="align: right"]14[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2:
[TABLE="width: 301"]
<tbody>[TR]
[TD][TABLE="width: 394"]
<tbody>[TR]
[TD]Subject[/TD]
[TD]Measure2 Date[/TD]
[TD]Value[/TD]
[TD]Matching Measure1 Date[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]3/16/2015[/TD]
[TD]35[/TD]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]2/28/2016[/TD]
[TD]64[/TD]
[TD]3/16/2015[/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD]1/24/2014[/TD]
[TD]13[/TD]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD]1/5/2016[/TD]
[TD]63[/TD]
[TD]1/24/2014[/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD]11/26/2017[/TD]
[TD]69[/TD]
[TD]1/24/2014[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]8/6/2014[/TD]
[TD]102[/TD]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]3/5/2017[/TD]
[TD]98[/TD]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]3/26/2018[/TD]
[TD]115[/TD]
[TD]8/6/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[cagriyuksel]

I want to correct the formula in my question, it's actually: =INDEX(Sheet1!B:B,MATCH(Sheet2!A2, IF(OR(DAY(Sheet2!B:B-Sheet1!B:B)<30, DAY(Sheet2!B:B-Sheet1!B:B)>-30), Sheet1!A:A),0))

[h=2][/h]

 

[oldbrewer]

I am in UK and dates go to day month year so 3/5/2017 is the same as 5/3/2017 and your dates appear as text - sorry

 

[cagriyuksel]

Hi. They go as mm/dd/yyyy

It looks like it's not allowed to attach an actual spreadsheet?