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
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Excel 2010
<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><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?
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
<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?
Last edited: