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
Principal Date. You can do this with an
INDEX-MATCH function with multiple
With your data in
A2:C11 and the column headers in
row 1, enter this formula in
D2 and fill down:
Note that you need to enter it as an array formula using CtrlShiftEnter.
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).
MATCH function looks for a value of
1 from the lookup array provided by multiplying multiple logical conditions. It will evaluate whether
A4=A2, etc. and create a column of
FALSE values. It will then do the same for
C3="Yes", etc. The product of the logical arrays will be
1 any time both conditions are satisfied. (
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: