Index/Match/Max Tenure

[Airam]

Good day

I was trying to get a tenure using a specific value in array. The Formula I'm using works fine but I encounter issue when the criteria I was looking for has a duplicate in Row

What I am trying to obtain is to get the tenure of an employee using the most recent data in a the array.

Table 1:
This is when the value ("-N1") doesn't have duplicate in a Row.
I was able to get accurate result which reflects in Column B

A	B	C	D	E	F	G	H	I
NAME	Tenure	Status	01/21/2018	01/28/2018	02/04/2018	02/11/2018	02/18/2018	02/25/2018
Employee A	157	Active	RIB-S2-T1	RIB-S2-T2	RIB-S2-T3	RIB-S2-N1	RIB-S2-N2

<tbody>
</tbody>

Formula Used to get the tenure (Column B):

=IFERROR(DATEDIF(IF(C2="Active",INDEX($D:$I,1,MATCH("*-N1*"D2:I2,0))),""),TODAY(),"D"),"")

<tbody>
</tbody>

Table 2:
The value ("-N1") have duplicate in a Row (G2 & M2). What I need is for the computation of tenure to be based on Column M2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

NAME

Tenure

Status

1/21/2018

1/28/2018

2/4/2018

2/11/2018

2/18/2018

2/25/2018

7/15/2018

7/22/2018

7/29/2018

8/5/2018

8/12/2018

8/19/2018

Employee A

Active

RIB-S2-T1

RIB-S2-T2

RIB-S2-T3

RIB-S2-N1

RIB-S2-N2

CAR-S2-T1

CAR-S2-T2

CAR-S2-T3

CAR-S2-N1

CAR-S2-N2

CAR-S2-N3

<tbody>
</tbody>

Hoping for your help on this.

 

[DRSteele]

This reports the maximum date from Row4 where the Status is Active and there is -N1 contained in the code. You will notice that the difference between today (jul18/2080) and aug5/201) is negative eighteen days.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	NAME	Tenure	Status	1/21/18	1/28/18	2/04/18	2/11/18	2/18/18	2/25/18	7/15/18	7/22/18	7/29/18	8/05/18	8/12/18	8/19/18
5	Employee A	-18	Active	RIB-S2-T1	RIB-S2-T2	RIB-S2-T3	RIB-S2-N1	RIB-S2-N2		CAR-S2-T1	CAR-S2-T2	CAR-S2-T3	CAR-S2-N1	CAR-S2-N2	CAR-S2-N3
Sheet16

Cell Formulas
Range	Formula
B5	{=IF(C5="Active",TODAY()-MAX(IF(IFERROR(SEARCH("-N1",D5:O5),0),D4:O4,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Airam]

Thank you very much.

Is there a way that if a value is less than 1, it will return a blank. instead of showing the negative number (-18).

 

[Airam]

Another follow up question is. If the status is not "Active" instead of False, how can I have the formula return as "Blank". Thank you very much in advance.