lookup date range or one earlier

[Dares2]

hi,
I am wanting to find a date in a range of dates and if its not there take the earlier date. This will then drive a sum offset indirect formula to pick up interest for the financial year.
Can anyone advise how to write this formula to find the exact date or one earlier? I would then question how I calculate total repayments since 30 June prior also?

Loans
Current Mth	28/02/2017
closest date or one immediately prior	28/02/2017	This formula not correct
Cell reference	Loan!A10	="Loan!"&VLOOKUP(B3,Loan!$A$9:$B$48,2,0)	This is ok
Last twelve months
Total Loan Repayments last 12mths	142,279.02
Loan Repayments since 30 June prior year
Total principle remaining			Thanks Darren
TABLE
Payment Date	Ref	Beginning Balance	Payment	Principal	Interest	Ending Balance
30/11/2016	A12	900,000.00	71,139.51	62,558.69	8,580.82	837,441.31
28/02/2017	A13	837,441.31	71,139.51	58,749.97	12,389.54	778,691.34
31/05/2017	A14	1,588,691.34	71,139.51	47,246.42	23,893.09	1,541,444.92
30/09/2017	A15	1,631,444.92	71,139.51	39,116.56	32,022.95	1,592,328.36
30/11/2017	A16	1,592,328.36	71,139.51	55,172.60	15,966.91	1,537,155.76
28/02/2018	A17	1,537,155.76	71,139.51	48,398.03	22,741.48	1,488,757.73
31/05/2018	A18	1,488,757.73	71,139.51	48,624.60	22,514.91	1,440,133.13
30/09/2018	A19	1,440,133.13	71,139.50	42,257.93	28,881.57	1,397,875.20
30/11/2018	A20	1,397,875.20	71,139.51	57,122.46	14,017.05	1,340,752.74
28/02/2019	A21	1,340,752.74	71,139.50	51,303.71	19,835.79	1,289,449.03
31/05/2019	A22	1,289,449.03	71,139.51	51,638.80	19,500.71	1,237,810.23
30/09/2019	A23	1,237,810.23	71,139.51	46,315.48	24,824.03	1,191,494.75
30/11/2019	A24	1,191,494.75	71,139.51	59,191.92	11,947.59	1,132,302.83
29/02/2020	A25	1,132,302.83	71,139.51	54,201.50	16,938.01	1,078,101.33
31/05/2020	A26	1,078,101.33	71,139.51	54,835.07	16,304.44	1,023,266.26
30/09/2020	A27	1,023,266.26	71,139.50	50,618.11	20,521.39	972,648.15
30/11/2020	A28	972,648.15	71,139.51	61,386.38	9,753.13	911,261.77

<colgroup><col width="260" style="width: 195pt; mso-width-source: userset; mso-width-alt: 9508;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <tbody> </tbody>

 

[Rada1]

Try this formula in B2 to find exact date or one earlier;

If your date is in A2, and your table is in A5:G50 (please adjust this ranges in formula)

=INDEX(A6:A50,MATCH(MIN(IF(A2-A6:A50>=0,(ABS(A2-A6:A50)))),ABS(A2-A6:A50),0))

Enter with Ctrl+Shift+Enter, not just Enter.

 

[Dares2]

Thanks so much Rada. This helps me and thanks for quick response too. Need to learn this stuff.

 

[Rada1]

You are welcome.

I figured that I made this ABS part redundant, so you can use this formula

=INDEX(A6:A50,MATCH(MIN(IF(A2-A6:A50>=0,(A2-A6:A50))),(A2-A6:A50),0))

Both work the same.

I'm also learning, good luck.

 

[Dares2]

Thanks again Rada. Your help has been invaluable to solve a problem quickly too. Thankgoodness for this great community too.