Clever Geek Handbook

Excel - select a cell based on the adjacent cell value

I have the following Excel spreadsheet and I am trying to figure out how I can write a formula to provide the values ​​in column D.

Each line has a test date, I am trying to calculate the difference per day from each test date to the main date defined for each object.

I am guessing this involves picking a major test date based on the value in column C, and I'm not sure how.

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
+3


source to share


3 answers


The logic is here: (1) Find the date for each object that is the primary date and return it for each row; and (2) subtract that date from the current date at col B

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

to Subject

and Principal Date

. You can do this with a INDEX-MATCH

multi criteria function MATCH

.

With your data in A2:C11

and column headers in, row 1

enter this formula in D2

and fill in:

{=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.

Function logic INDEX-MATCH

:



  • A2:C11

    - your entire pivot table of data; the function looks through this entire table.
    • Note that you can include headers if you like, which can be helpful in defining the search column with an appropriate column header header. If you do this, you must make sure that all of your arrays are the same size (i.e. if your table is data A1:C11

      , your columns in the function MATCH

      should start with row 1

      ).
  • The function MATCH

    looks for a value 1

    from a lookup array provided by multiplying a set of boolean conditions. It will assess whether A2=A2

    , A3=A2

    , A4=A2

    etc. And create a value column TRUE

    / FALSE

    . Then it will do the same for C2="Yes"

    , C3="Yes"

    etc. The logical array product will occur 1

    at any time if both conditions are met. ( 0

    tells MATCH

    you to search for an exact match.)
  • 2

    tells the function INDEX

    to find the value in the second column ( B

    ) in the row specified by the function MATCH

    - that is, where both conditions are met.

This value is then subtracted from the value in B2

to indicate the date difference.

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

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

+3


source


Basically the same logic as @Brendan but a little shorter. This is still an array formula introduced with Ctrl+ Shift+Enter

=B2-SMALL(IF(IF($A$2:$A$11=A2,$C$2:$C$11)="Yes",$B$2:$B$11),1)


The inner one IF($A$2:$A$11=A2,$C$2:$C$11)

gets an array with only the director that matches the subject you are in. The next one IF(IF(...)="Yes",$B$2:$B$11)

gets an array with only the base date and FALSE for all other values. SMALL(...,1)

only gets you the base date, which you can subtract from the current test date:B2-SMALL(...)

+1


source


Extract DAY () from TestDate and add Day Difference:

Formula for Main Date column: = DAY (B2) + D2

     A           B           C                 D
1    Subject     TestDate    Principal date    Day difference from Principal
2    Subject 1   2014-06-01  28                -3

You now have the correct day and you can create a new date in a new column or combine the two functions.

0


source






More articles:


All Articles