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:

Subject	Measure1 Date	Value
David	3/27/2015	29
David	2/28/2016	48
David	4/1/2017	8
Maria	1/15/2014	13
Maria	11/18/2016	47
Bob	3/5/2017	36
Bob	3/6/2018	14

<tbody>
</tbody>

Sheet2:

Subject Measure2 Date Value Matching Measure1 Date David 3/16/2015 35 #NUM! David 2/28/2016 64 3/16/2015 Maria 1/24/2014 13 #NUM! Maria 1/5/2016 63 1/24/2014 Maria 11/26/2017 69 1/24/2014 Bob 8/6/2014 102 #NUM! Bob 3/5/2017 98 #NUM! Bob 3/26/2018 115 8/6/2014 <tbody> </tbody>

<tbody>
</tbody>

 

[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?