Index(match given ID and specific date

[Henk85]

Hey guys, I'm looking for a formula that enables me to let Excel search for a specific DR value, given two criteria (Company ticker and Date closest)

Excel 2010

	B	C	D	G	Q	R
3	Date Effective/ Unconditional	Date closest before	Date closest after	Acquiror Primary Ticker Symbol		Match DR - column AJ with Ticker and date closest
5	05/06/2008	31/12/2007		IR	1	0
6	21/04/2008	31/12/2007		NOV	1	0
7	17/03/2008			ONNN	1	#N/A
11	31/03/2008			VSE	1	#N/A
13	28/02/2008	31/12/2007		RTIX	1	#REF!
19	14/11/2007	31/10/2007		ABM	1
21	14/12/2007	31/12/2006	31/12/2007	ARRS	1	0
23	18/02/2009			SFLK	1	#N/A
26	05/11/2007	31/12/2006	31/12/2007	TNB	1	#REF!
35	04/10/2007	31/12/2006	31/12/2007	EVVV	1	0
36	15/10/2007	31/12/2006	31/12/2007	LKQ	1	0
37	01/10/2007			ENR	1	#N/A
40	27/12/2007			CTV	1	#N/A
43	01/11/2007	31/12/2006	31/12/2007	CYBS	1	0
44	11/12/2007			DVR	1	#N/A
47	17/08/2007	31/01/2007		PSS	1	0
49	22/10/2007	30/09/2007		HOLX	1	0
52	25/08/2007	31/03/2007		EXAR	1	0
55	09/08/2007	31/12/2006	31/12/2007	JAH	1	0
66	06/04/2007	31/12/2006	31/12/2007	AIMC	1	0
67	16/11/2007	31/12/2006	31/12/2007	VMC	1	#REF!
71	26/03/2007	31/12/2006	31/12/2007	PLCM	1	#REF!
75	07/03/2007	31/12/2006	31/12/2007	CVO	1	0
77	01/05/2007	31/12/2006	31/12/2007	MFW	1	0
78	17/04/2007	30/09/2006	30/09/2007	IDEV	1	0
80	02/04/2007	31/12/2006	31/12/2007	LSI	1	0
81	29/01/2007	31/05/2006	31/05/2007	ANGO	1	0
82	19/03/2007	31/12/2006	31/12/2007	FCX	1	0
89	09/01/2007	31/12/2006	31/12/2007	RRD	1
91	01/12/2006	30/06/2006	30/06/2007	VRCC	1	#REF!
92	08/01/2007	31/12/2006	31/12/2007	BHE	1	0
93	03/01/2007	31/12/2006	31/12/2007	LVLT	1	0
95	08/12/2006	30/09/2006	30/09/2007	OSK	1	0
105	31/05/2007	31/12/2006	31/12/2007	WNR	1	#REF!
107	10/01/2007			PGODQ	1	#N/A
108	31/10/2006			SMDI	1	#N/A
110	29/01/2007	31/10/2006	31/10/2007	BRCD	1	0
114	21/02/2007			WPS	1	#N/A
123	09/11/2006	31/12/2005	31/12/2006	TMO	1	#REF!
125	22/08/2006	31/03/2006	31/03/2007	QTM	1	#REF!
127	14/07/2006	31/12/2005	31/12/2006	MIL	1	0
128	06/07/2006	31/12/2005	31/12/2006	JDAS	1	0
132	16/08/2006			EPIX	1	#N/A
133	06/11/2006			WPI	1	#N/A
136	09/08/2006	31/12/2005	31/12/2006	CALP	1	0
139	29/08/2006			VISG	1	#N/A
142	01/01/2006	31/12/2005	31/12/2006	KNXA	1	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

MA​

Excel 2010

	A	N	AH	AI	AJ
1	Company Name	Ticker Symbol	DR counting once v2		Datadate adj
3	3DFX INTERACTIVE INC	TDFX	0		31/12/1998
6	3DFX INTERACTIVE INC	TDFX	0		31/01/2000
8	3DFX INTERACTIVE INC	TDFX	0		31/01/2001
10	ABM INDUSTRIES INC	ABM	0.324847691		31/10/1997
21	ABM INDUSTRIES INC	ABM	0.316078473		31/10/1998
39	ABM INDUSTRIES INC	ABM	0.389084086		31/10/1999
60	ABM INDUSTRIES INC	ABM	0.395713004		31/10/2000
84	ABM INDUSTRIES INC	ABM	0.404496982		31/10/2001
107	ABM INDUSTRIES INC	ABM	0.429129553		31/10/2002
129	ABM INDUSTRIES INC	ABM	0.399932675		31/10/2003
149	ABM INDUSTRIES INC	ABM	0.386775255		31/10/2004
168	ABM INDUSTRIES INC	ABM	0.381062089		31/10/2005
186	ABM INDUSTRIES INC	ABM	0.379247303		31/10/2006
203	ABM INDUSTRIES INC	ABM	0.377698486		31/10/2007
219	ADVANCED ENERGY INDS INC	AEIS	0		31/12/1997
220	ADVANCED ENERGY INDS INC	AEIS	0		31/12/1998
223	ADVANCED ENERGY INDS INC	AEIS	0		31/12/1999
226	ADVANCED ENERGY INDS INC	AEIS	0		31/12/2000
229	ADVANCED ENERGY INDS INC	AEIS	0		31/12/2001
232	ADVANCED ENERGY INDS INC	AEIS	0		31/12/2002
235	ADVANCED ENERGY INDS INC	AEIS	0		31/12/2003
238	ADVANCED ENERGY INDS INC	AEIS	0		31/12/2004
241	ADVANCED ENERGY INDS INC	AEIS	0		31/12/2005
244	ADVANCED ENERGY INDS INC	AEIS	0		31/12/2006
247	ADVANCED ENERGY INDS INC	AEIS	0		31/12/2007
250	ADVANCED MEDICAL OPTICS INC	EYE	0		31/12/2001
253	ADVANCED MEDICAL OPTICS INC	EYE	0		31/12/2002
256	ADVANCED MEDICAL OPTICS INC	EYE	0		31/12/2003
266	ADVANCED MEDICAL OPTICS INC	EYE	0		31/12/2004
280	ADVANCED MEDICAL OPTICS INC	EYE	0		31/12/2005
298	ADVANCED MEDICAL OPTICS INC	EYE	0		31/12/2006
313	ADVANCED MEDICAL OPTICS INC	EYE	0		31/12/2007
323	AEROFLEX INC	ARXX	0.629991496		30/06/1997
326	AEROFLEX INC	ARXX	0.572029351		30/06/1998
335	AEROFLEX INC	ARXX	0.602632798		30/06/1999
346	AEROFLEX INC	ARXX	0.625218194		30/06/2000
358	AEROFLEX INC	ARXX	0.617498245		30/06/2001
369	AEROFLEX INC	ARXX	0.668306247		30/06/2002
379	AEROFLEX INC	ARXX	0.653546283		30/06/2003
389	AEROFLEX INC	ARXX	0.671905408		30/06/2004
395	AEROFLEX INC	ARXX	0.679145117		30/06/2005
398	AEROFLEX INC	ARXX	0.685139901		30/06/2006
401	AGENUS INC	AGEN	0		31/12/1998
404	AGENUS INC	AGEN	0		31/12/1999
407	AGENUS INC	AGEN	0		31/12/2000
410	AGENUS INC	AGEN	0		31/12/2001
413	AGENUS INC	AGEN	0		31/12/2002
416	AGENUS INC	AGEN	0		31/12/2003
419	AGENUS INC	AGEN	0		31/12/2004
422	AGENUS INC	AGEN	0		31/12/2005
425	AGENUS INC	AGEN	0		31/12/2006
428	AGENUS INC	AGEN	0		31/12/2007
431	AK STEEL HOLDING CORP	AKS	0		31/12/1997
432	AK STEEL HOLDING CORP	AKS	0		31/12/1998

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

DR​

Worksheet Formulas Cell Formula AH3 =IF(AND(G3=H3,COUNTIFS($A$3:A3,A3,$G$3:G3,G3,$H$3:H3,G3)=1),AF3,"") AJ3 =DATE(LEFT(G3,4), MID(G3,5,2), RIGHT(G3,2)) AH6 =IF(AND(G6=H6,COUNTIFS($A$3:A6,A6,$G$3:G6,G6,$H$3:H6,G6)=1),AF6,"") AJ6 =DATE(LEFT(G6,4), MID(G6,5,2), RIGHT(G6,2)) AH8 =IF(AND(G8=H8,COUNTIFS($A$3:A8,A8,$G$3:G8,G8,$H$3:H8,G8)=1),AF8,"") AJ8 =DATE(LEFT(G8,4), MID(G8,5,2), RIGHT(G8,2)) AH10 =IF(AND(G10=H10,COUNTIFS($A$3:A10,A10,$G$3:G10,G10,$H$3:H10,G10)=1),AF10,"") AJ10 =DATE(LEFT(G10,4), MID(G10,5,2), RIGHT(G10,2)) AH21 =IF(AND(G21=H21,COUNTIFS($A$3:A21,A21,$G$3:G21,G21,$H$3:H21,G21)=1),AF21,"") AJ21 =DATE(LEFT(G21,4), MID(G21,5,2), RIGHT(G21,2)) AH39 =IF(AND(G39=H39,COUNTIFS($A$3:A39,A39,$G$3:G39,G39,$H$3:H39,G39)=1),AF39,"") AJ39 =DATE(LEFT(G39,4), MID(G39,5,2), RIGHT(G39,2)) AH60 =IF(AND(G60=H60,COUNTIFS($A$3:A60,A60,$G$3:G60,G60,$H$3:H60,G60)=1),AF60,"") AJ60 =DATE(LEFT(G60,4), MID(G60,5,2), RIGHT(G60,2)) AH84 =IF(AND(G84=H84,COUNTIFS($A$3:A84,A84,$G$3:G84,G84,$H$3:H84,G84)=1),AF84,"") AJ84 =DATE(LEFT(G84,4), MID(G84,5,2), RIGHT(G84,2)) AH107 =IF(AND(G107=H107,COUNTIFS($A$3:A107,A107,$G$3:G107,G107,$H$3:H107,G107)=1),AF107,"") AJ107 =DATE(LEFT(G107,4), MID(G107,5,2), RIGHT(G107,2)) AH129 =IF(AND(G129=H129,COUNTIFS($A$3:A129,A129,$G$3:G129,G129,$H$3:H129,G129)=1),AF129,"") AJ129 =DATE(LEFT(G129,4), MID(G129,5,2), RIGHT(G129,2)) AH149 =IF(AND(G149=H149,COUNTIFS($A$3:A149,A149,$G$3:G149,G149,$H$3:H149,G149)=1),AF149,"") AJ149 =DATE(LEFT(G149,4), MID(G149,5,2), RIGHT(G149,2)) AH168 =IF(AND(G168=H168,COUNTIFS($A$3:A168,A168,$G$3:G168,G168,$H$3:H168,G168)=1),AF168,"") AJ168 =DATE(LEFT(G168,4), MID(G168,5,2), RIGHT(G168,2)) AH186 =IF(AND(G186=H186,COUNTIFS($A$3:A186,A186,$G$3:G186,G186,$H$3:H186,G186)=1),AF186,"") AJ186 =DATE(LEFT(G186,4), MID(G186,5,2), RIGHT(G186,2)) AH203 =IF(AND(G203=H203,COUNTIFS($A$3:A203,A203,$G$3:G203,G203,$H$3:H203,G203)=1),AF203,"") AJ203 =DATE(LEFT(G203,4), MID(G203,5,2), RIGHT(G203,2)) AH219 =IF(AND(G219=H219,COUNTIFS($A$3:A219,A219,$G$3:G219,G219,$H$3:H219,G219)=1),AF219,"") AH220 =IF(AND(G220=H220,COUNTIFS($A$3:A220,A220,$G$3:G220,G220,$H$3:H220,G220)=1),AF220,"") AJ219 =DATE(LEFT(G219,4), MID(G219,5,2), RIGHT(G219,2)) AJ220 =DATE(LEFT(G220,4), MID(G220,5,2), RIGHT(G220,2)) AH223 =IF(AND(G223=H223,COUNTIFS($A$3:A223,A223,$G$3:G223,G223,$H$3:H223,G223)=1),AF223,"") AJ223 =DATE(LEFT(G223,4), MID(G223,5,2), RIGHT(G223,2)) AH226 =IF(AND(G226=H226,COUNTIFS($A$3:A226,A226,$G$3:G226,G226,$H$3:H226,G226)=1),AF226,"") AJ226 =DATE(LEFT(G226,4), MID(G226,5,2), RIGHT(G226,2)) AH229 =IF(AND(G229=H229,COUNTIFS($A$3:A229,A229,$G$3:G229,G229,$H$3:H229,G229)=1),AF229,"") AJ229 =DATE(LEFT(G229,4), MID(G229,5,2), RIGHT(G229,2)) AH232 =IF(AND(G232=H232,COUNTIFS($A$3:A232,A232,$G$3:G232,G232,$H$3:H232,G232)=1),AF232,"") AJ232 =DATE(LEFT(G232,4), MID(G232,5,2), RIGHT(G232,2)) AH235 =IF(AND(G235=H235,COUNTIFS($A$3:A235,A235,$G$3:G235,G235,$H$3:H235,G235)=1),AF235,"") AJ235 =DATE(LEFT(G235,4), MID(G235,5,2), RIGHT(G235,2)) AH238 =IF(AND(G238=H238,COUNTIFS($A$3:A238,A238,$G$3:G238,G238,$H$3:H238,G238)=1),AF238,"") AJ238 =DATE(LEFT(G238,4), MID(G238,5,2), RIGHT(G238,2)) AH241 =IF(AND(G241=H241,COUNTIFS($A$3:A241,A241,$G$3:G241,G241,$H$3:H241,G241)=1),AF241,"") AJ241 =DATE(LEFT(G241,4), MID(G241,5,2), RIGHT(G241,2)) AH244 =IF(AND(G244=H244,COUNTIFS($A$3:A244,A244,$G$3:G244,G244,$H$3:H244,G244)=1),AF244,"") AJ244 =DATE(LEFT(G244,4), MID(G244,5,2), RIGHT(G244,2)) AH247 =IF(AND(G247=H247,COUNTIFS($A$3:A247,A247,$G$3:G247,G247,$H$3:H247,G247)=1),AF247,"") AJ247 =DATE(LEFT(G247,4), MID(G247,5,2), RIGHT(G247,2)) AH250 =IF(AND(G250=H250,COUNTIFS($A$3:A250,A250,$G$3:G250,G250,$H$3:H250,G250)=1),AF250,"") AJ250 =DATE(LEFT(G250,4), MID(G250,5,2), RIGHT(G250,2)) AH253 =IF(AND(G253=H253,COUNTIFS($A$3:A253,A253,$G$3:G253,G253,$H$3:H253,G253)=1),AF253,"") AJ253 =DATE(LEFT(G253,4), MID(G253,5,2), RIGHT(G253,2)) AH256 =IF(AND(G256=H256,COUNTIFS($A$3:A256,A256,$G$3:G256,G256,$H$3:H256,G256)=1),AF256,"") AJ256 =DATE(LEFT(G256,4), MID(G256,5,2), RIGHT(G256,2)) AH266 =IF(AND(G266=H266,COUNTIFS($A$3:A266,A266,$G$3:G266,G266,$H$3:H266,G266)=1),AF266,"") AJ266 =DATE(LEFT(G266,4), MID(G266,5,2), RIGHT(G266,2)) AH280 =IF(AND(G280=H280,COUNTIFS($A$3:A280,A280,$G$3:G280,G280,$H$3:H280,G280)=1),AF280,"") AJ280 =DATE(LEFT(G280,4), MID(G280,5,2), RIGHT(G280,2)) AH298 =IF(AND(G298=H298,COUNTIFS($A$3:A298,A298,$G$3:G298,G298,$H$3:H298,G298)=1),AF298,"") AJ298 =DATE(LEFT(G298,4), MID(G298,5,2), RIGHT(G298,2)) AH313 =IF(AND(G313=H313,COUNTIFS($A$3:A313,A313,$G$3:G313,G313,$H$3:H313,G313)=1),AF313,"") AJ313 =DATE(LEFT(G313,4), MID(G313,5,2), RIGHT(G313,2)) AH323 =IF(AND(G323=H323,COUNTIFS($A$3:A323,A323,$G$3:G323,G323,$H$3:H323,G323)=1),AF323,"") AJ323 =DATE(LEFT(G323,4), MID(G323,5,2), RIGHT(G323,2)) AH326 =IF(AND(G326=H326,COUNTIFS($A$3:A326,A326,$G$3:G326,G326,$H$3:H326,G326)=1),AF326,"") AJ326 =DATE(LEFT(G326,4), MID(G326,5,2), RIGHT(G326,2)) AH335 =IF(AND(G335=H335,COUNTIFS($A$3:A335,A335,$G$3:G335,G335,$H$3:H335,G335)=1),AF335,"") AJ335 =DATE(LEFT(G335,4), MID(G335,5,2), RIGHT(G335,2)) AH346 =IF(AND(G346=H346,COUNTIFS($A$3:A346,A346,$G$3:G346,G346,$H$3:H346,G346)=1),AF346,"") AJ346 =DATE(LEFT(G346,4), MID(G346,5,2), RIGHT(G346,2)) AH358 =IF(AND(G358=H358,COUNTIFS($A$3:A358,A358,$G$3:G358,G358,$H$3:H358,G358)=1),AF358,"") AJ358 =DATE(LEFT(G358,4), MID(G358,5,2), RIGHT(G358,2)) AH369 =IF(AND(G369=H369,COUNTIFS($A$3:A369,A369,$G$3:G369,G369,$H$3:H369,G369)=1),AF369,"") AJ369 =DATE(LEFT(G369,4), MID(G369,5,2), RIGHT(G369,2)) AH379 =IF(AND(G379=H379,COUNTIFS($A$3:A379,A379,$G$3:G379,G379,$H$3:H379,G379)=1),AF379,"") AJ379 =DATE(LEFT(G379,4), MID(G379,5,2), RIGHT(G379,2)) AH389 =IF(AND(G389=H389,COUNTIFS($A$3:A389,A389,$G$3:G389,G389,$H$3:H389,G389)=1),AF389,"") AJ389 =DATE(LEFT(G389,4), MID(G389,5,2), RIGHT(G389,2)) AH395 =IF(AND(G395=H395,COUNTIFS($A$3:A395,A395,$G$3:G395,G395,$H$3:H395,G395)=1),AF395,"") AJ395 =DATE(LEFT(G395,4), MID(G395,5,2), RIGHT(G395,2)) AH398 =IF(AND(G398=H398,COUNTIFS($A$3:A398,A398,$G$3:G398,G398,$H$3:H398,G398)=1),AF398,"") AJ398 =DATE(LEFT(G398,4), MID(G398,5,2), RIGHT(G398,2)) AH401 =IF(AND(G401=H401,COUNTIFS($A$3:A401,A401,$G$3:G401,G401,$H$3:H401,G401)=1),AF401,"") AJ401 =DATE(LEFT(G401,4), MID(G401,5,2), RIGHT(G401,2)) AH404 =IF(AND(G404=H404,COUNTIFS($A$3:A404,A404,$G$3:G404,G404,$H$3:H404,G404)=1),AF404,"") AJ404 =DATE(LEFT(G404,4), MID(G404,5,2), RIGHT(G404,2)) AH407 =IF(AND(G407=H407,COUNTIFS($A$3:A407,A407,$G$3:G407,G407,$H$3:H407,G407)=1),AF407,"") AJ407 =DATE(LEFT(G407,4), MID(G407,5,2), RIGHT(G407,2)) AH410 =IF(AND(G410=H410,COUNTIFS($A$3:A410,A410,$G$3:G410,G410,$H$3:H410,G410)=1),AF410,"") AJ410 =DATE(LEFT(G410,4), MID(G410,5,2), RIGHT(G410,2)) AH413 =IF(AND(G413=H413,COUNTIFS($A$3:A413,A413,$G$3:G413,G413,$H$3:H413,G413)=1),AF413,"") AJ413 =DATE(LEFT(G413,4), MID(G413,5,2), RIGHT(G413,2)) AH416 =IF(AND(G416=H416,COUNTIFS($A$3:A416,A416,$G$3:G416,G416,$H$3:H416,G416)=1),AF416,"") AJ416 =DATE(LEFT(G416,4), MID(G416,5,2), RIGHT(G416,2)) AH419 =IF(AND(G419=H419,COUNTIFS($A$3:A419,A419,$G$3:G419,G419,$H$3:H419,G419)=1),AF419,"") AJ419 =DATE(LEFT(G419,4), MID(G419,5,2), RIGHT(G419,2)) AH422 =IF(AND(G422=H422,COUNTIFS($A$3:A422,A422,$G$3:G422,G422,$H$3:H422,G422)=1),AF422,"") AJ422 =DATE(LEFT(G422,4), MID(G422,5,2), RIGHT(G422,2)) AH425 =IF(AND(G425=H425,COUNTIFS($A$3:A425,A425,$G$3:G425,G425,$H$3:H425,G425)=1),AF425,"") AJ425 =DATE(LEFT(G425,4), MID(G425,5,2), RIGHT(G425,2)) AH428 =IF(AND(G428=H428,COUNTIFS($A$3:A428,A428,$G$3:G428,G428,$H$3:H428,G428)=1),AF428,"") AJ428 =DATE(LEFT(G428,4), MID(G428,5,2), RIGHT(G428,2)) AH431 =IF(AND(G431=H431,COUNTIFS($A$3:A431,A431,$G$3:G431,G431,$H$3:H431,G431)=1),AF431,"") AH432 =IF(AND(G432=H432,COUNTIFS($A$3:A432,A432,$G$3:G432,G432,$H$3:H432,G432)=1),AF432,"") AJ431 =DATE(LEFT(G431,4), MID(G431,5,2), RIGHT(G431,2)) AJ432 =DATE(LEFT(G432,4), MID(G432,5,2), RIGHT(G432,2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I've come up with this formula:
=INDEX(DR!$A$2:$AJ$10000,MATCH(1,(DR!$N$3:$N$13788=G5)*(DR!$AJ$3:$AJ$13788=C5),0),33)
Incl ctrl+shift+enter,

So for date closest, ABM should for instance return 0.3777

But it does not really work as it should be working.

Any suggestions?

 

[AhoyNC]

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.

=INDEX(DR!$A$2:$AJ$10000,MATCH(MIN(IF(DR!$N$3:$N$13788=$G5,ABS(DR!$AJ$3:$AJ$13788-$C5))),IF(DR!$N$3:$N$13788=$G5,ABS(DR!$AJ$3:$AJ$13788-$C5)),0),33)

 

[Henk85]

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.

=INDEX(DR!$A$2:$AJ$10000,MATCH(MIN(IF(DR!$N$3:$N$13788=$G5,ABS(DR!$AJ$3:$AJ$13788-$C5))),IF(DR!$N$3:$N$13788=$G5,ABS(DR!$AJ$3:$AJ$13788-$C5)),0),33)

Thank you for the reply. Unfortunately the formula is not working.
Why do you use MIN + ABS by the way, I only need an exact match as I've already calculated the right date : )

Thanks for thinking with me!

I think the only thing I need is a formula that allows two maching conditions (date and company ticker) and then selects the first number in DR cell!

 

[Henk85]

=INDEX(DR!$A$2:$AG$10000,MATCH(G5&C5,DR!$N$3:$N$13788&DR!$AJ$3:$AJ$13788,0),MATCH(DR!AH1,DR!1:1,0))

is also not working.

 

[AhoyNC]

When you said closest date I assumed the dates didn't have an exact match.
In looking at your first formula your INDEX range is different from the MATCH range. INDEX starts at A2 and only goes to row 10000, where MATCH starts at N3 goes down to row 13788.

 

[Henk85]

I',m aware of the range, thx. I changed this, but with no effect.

There is a exact date match

Column c matches AJ
Column G matches AH

 

[AhoyNC]

See if this example helps.
I just used a small sample, so the #NA is just where there was no match. Your ranges do need to be the same size our the row number feed to INDEX will be off.

Excel Workbook
	A	B	C	D	G	Q	R	S
3		DateEffective/Unconditional	Date closest before	Date closest after	Ticker		Match
4
5		3/17/2008			ONNN		#N/A
6		3/31/2018			VSE		#N/A
7		2/28/2008	12/31/2007		RTIX		#N/A
8		11/14/2008	10/31/2007		ABM		0.377698
9		12/14/2018	12/31/2017		ARRS		#N/A
Sheet1

Excel Workbook
	A	B	C	D	E	M	N	AG	AH	AI	AJ
1	Company Name						Ticker Symbol		DR counting once v2		Datadate adj
2
3	3DFX INTERACTIVE INC						TDFX		0		12/31/1998
4	3DFX INTERACTIVE INC						TDFX		0		1/31/2000
5	3DFX INTERACTIVE INC						TDFX		0		1/31/2001
6	ABM INDUSTRIES INC						ABM		0.324847691		10/31/1997
7	ABM INDUSTRIES INC						ABM		0.316078473		10/31/1998
8	ABM INDUSTRIES INC						ABM		0.389084086		10/31/1999
9	ABM INDUSTRIES INC						ABM		0.395713004		10/31/2000
10	ABM INDUSTRIES INC						ABM		0.404496982		10/31/2001
11	ABM INDUSTRIES INC						ABM		0.429129553		10/31/2002
12	ABM INDUSTRIES INC						ABM		0.399932675		10/31/2003
13	ABM INDUSTRIES INC						ABM		0.386775255		10/31/2004
14	ABM INDUSTRIES INC						ABM		0.381062089		10/31/2005
15	ABM INDUSTRIES INC						ABM		0.379247303		10/31/2006
16	ABM INDUSTRIES INC						ABM		0.377698486		10/31/2007
17	ADVANCED ENERGY INDS INC						AEIS		0		12/31/1997
18	ADVANCED ENERGY INDS INC						AEIS		0		12/31/1998
19	ADVANCED ENERGY INDS INC						AEIS		0		12/31/1999
20	ADVANCED ENERGY INDS INC						AEIS		0		12/31/2000
DR

 

[Henk85]

It works! Thanks a lot!

 

[AhoyNC]

You're welcome. Thanks for the feedback.