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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]Tenure[/TD]
[TD]Status[/TD]
[TD]01/21/2018[/TD]
[TD]01/28/2018[/TD]
[TD]02/04/2018[/TD]
[TD]02/11/2018[/TD]
[TD]02/18/2018[/TD]
[TD]02/25/2018[/TD]
[/TR]
[TR]
[TD]Employee A[/TD]
[TD]157[/TD]
[TD]Active[/TD]
[TD="align: center"]RIB-S2-T1[/TD]
[TD="align: center"]RIB-S2-T2[/TD]
[TD="align: center"]RIB-S2-T3[/TD]
[TD="align: center"]RIB-S2-N1[/TD]
[TD="align: center"]RIB-S2-N2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]







Formula Used to get the tenure (Column B):

[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]=IFERROR(DATEDIF(IF(C2="Active",INDEX($D:$I,1,MATCH("*-N1*"D2:I2,0))),""),TODAY(),"D"),"")[/TD]
[/TR]
</tbody>[/TABLE]


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


[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]Tenure[/TD]
[TD]Status[/TD]
[TD]1/21/2018[/TD]
[TD]1/28/2018[/TD]
[TD]2/4/2018[/TD]
[TD]2/11/2018[/TD]
[TD]2/18/2018[/TD]
[TD]2/25/2018[/TD]
[TD]7/15/2018[/TD]
[TD]7/22/2018[/TD]
[TD]7/29/2018[/TD]
[TD]8/5/2018[/TD]
[TD]8/12/2018[/TD]
[TD]8/19/2018[/TD]
[/TR]
[TR]
[TD]Employee A[/TD]
[TD][/TD]
[TD]Active[/TD]
[TD]RIB-S2-T1[/TD]
[TD]RIB-S2-T2[/TD]
[TD]RIB-S2-T3[/TD]
[TD]RIB-S2-N1


[/TD]
[TD]RIB-S2-N2[/TD]
[TD][/TD]
[TD]CAR-S2-T1[/TD]
[TD]CAR-S2-T2[/TD]
[TD]CAR-S2-T3[/TD]
[TD]CAR-S2-N1[/TD]
[TD]CAR-S2-N2[/TD]
[TD]CAR-S2-N3[/TD]
[/TR]
</tbody>[/TABLE]








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.