Mach Closest Date and enter a value

[Loukas]

Hi,

I have two array columns; column A has dates and column B has values. I have onther column C with dates which are not always mach with the dates on column A. I want a formula which will look at column A and mach the date of columns A and C, if the dates are not matching look for previous closest date and put the value in column D.
A B
<table border="0" cellpadding="0" cellspacing="0" width="139"><colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt; width:56pt" align="right" height="20" width="75">31/03/2011
</td> <td style="width:48pt" align="right" width="64">69</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">28/02/2011</td> <td align="right">67</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">31/01/2011</td> <td align="right">63</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">31/12/2010</td> <td align="right">69</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">30/11/2010</td> <td align="right">68</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">31/10/2010</td> <td align="right">59</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">30/09/2010</td> <td align="right">55</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">31/08/2010</td> <td align="right">49</td> </tr> </tbody></table>
The result it will be like that
C D
2/04/2011 69
3/03/2011 67
29/02/2011 67
30/01/2011 69

Thanks in advance

 

[Jonmo1]

Welcome to the board...

If you can sort your data ASCENDING by column A, then you can use this in D1

=INDEX(B:B,MATCH(C1,A:A,1))

Hope that helps.

 

[Loukas]

Thanks for your prompt reply.
I cannot sort the data. Both dates are starting from the most recent.

I think the solution is a combination of index and match functions

 

[T. Valko]

Thanks for your prompt reply.
I cannot sort the data. Both dates are starting from the most recent.

I think the solution is a combination of index and match functions

Try this...

Book1
	A	B	C	D	E
2	6/8/2001	10	_	2/11/2009	22
3	8/23/2001	94	_	10/13/2007	6
4	12/3/2002	60	_	11/17/2001	94
5	11/12/2004	100	_	10/25/2007	6
6	6/26/2006	6	_	2/9/2008	22
7	11/28/2007	22	_	_	_
8	2/26/2009	16	_	_	_
9	2/22/2010	7	_	_	_
10	8/30/2012	4	_	_	_
Sheet1

This array formula entered in E2 and copied down:

=INDEX(B$2:B$10,MATCH(MAX(IF(A$2:A$10<=D2,A$2:A$10)),A$2:A$10,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Note that I sorted the dates in column A just to make it easier to see what result you will get.

 

[Loukas]

Thanks man!!!

The formula is exactly what I was looking for!!!!!