I have the following excel spreadsheet and I am trying to work out how I can write a formula in order to provide the values in column D.

In each row, there is a test date, I am trying to calculate the day difference from each test date to the principal date, specific to each subject.

I assume this would involve selecting the principal test date based on the value in column C and I am not sure how I can do this.

Any help would be appreciated.

```
A B C D
Subject TestDate Principal date Day difference from Principal date
Subject 1 01/12/2014 -3
Subject 1 02/12/2014 -2
Subject 1 03/12/2014 -1
Subject 1 04/12/2014 Yes 0
Subject 2 07/12/2014 -1
Subject 2 08/12/2014 Yes 0
Subject 2 11/12/2014 3
Subject 3 17/12/2014 -1
Subject 3 18/12/2014 Yes 0
Subject 3 24/12/2014 6
```

# Best How To :

The logic here is: (1) Find the date for each subject that is the principal date, and return it for each row; and (2) subtract this date from the current date in `col B`

. (2) is easy, but (1) requires a way to match the value in `B`

on both `Subject`

and `Principal Date`

. You can do this with an `INDEX-MATCH`

function with multiple `MATCH`

criteria.

With your data in `A2:C11`

and the column headers in `row 1`

, enter this formula in `D2`

and fill down:

`{=B2-INDEX($A$2:$C$11,MATCH(1,($A$2:$A$11=$A2)*($C$2:$C$11="Yes"),0), 2)}`

Note that you need to enter it as an array formula using `Ctrl``Shift``Enter`.

The logic behind the `INDEX-MATCH`

function is:

`A2:C11`

is your entire raw data table; the function looks over this entire table.
- Note that you can include headers if you want, which can be useful in defining your lookup column by a matching column header name. If you do this, you need to make sure all of your arrays are of the same dimensions (
*i.e.*, if your data table is `A1:C11`

, your columns in the `MATCH`

function need to start in `row 1`

as well).

- The
`MATCH`

function looks for a value of `1`

from the lookup array provided by multiplying multiple logical conditions. It will evaluate whether `A2=A2`

, `A3=A2`

, `A4=A2`

, etc. and create a column of `TRUE`

/`FALSE`

values. It will then do the same for `C2="Yes"`

, `C3="Yes"`

, etc. The product of the logical arrays will be `1`

any time both conditions are satisfied. (`0`

tells `MATCH`

to look for an *exact* match.)
`2`

tells the `INDEX`

function to find the value in the second column (`B`

) in the *row* specified by the `MATCH`

function -- *i.e.*, where both conditions are met.

This value is then subtracted from the value in `B2`

to give the date difference.

As noted in the comments, this formula can also be simplified to index only the desired lookup column:

`=B2-INDEX($B$2:$B$11,MATCH(1,($A$2:$A$11=$A2)*($C$2:$C$11="Yes"),0))`