lookup date range or one earlier

Dares2

New Member
Joined
Feb 27, 2017
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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?

[TABLE="width: 538"]
<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>[TR]
[TD="class: xl60867, width: 260, bgcolor: transparent"]Loans[/TD]
[TD="class: xl60872, width: 71, bgcolor: transparent"][/TD]
[TD="width: 100, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Current Mth[/TD]
[TD="class: xl60868, bgcolor: transparent, align: right"]28/02/2017[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl60874, bgcolor: transparent"]closest date or one immediately prior[/TD]
[TD="class: xl60875, bgcolor: yellow, align: right"]28/02/2017[/TD]
[TD="bgcolor: transparent, colspan: 2"]This formula not correct[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Cell reference[/TD]
[TD="class: xl60868, bgcolor: transparent"]Loan!A10[/TD]
[TD="bgcolor: transparent, colspan: 3"]="Loan!"&VLOOKUP(B3,Loan!$A$9:$B$48,2,0)[/TD]
[TD="bgcolor: transparent"]This is ok[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl60867, bgcolor: transparent"]Last twelve months[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Total Loan Repayments last 12mths[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]142,279.02[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loan Repayments since 30 June prior year[/TD]
[TD="class: xl60873, bgcolor: yellow"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Total principle remaining[/TD]
[TD="class: xl60873, bgcolor: yellow"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]

Thanks Darren[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl60876, bgcolor: transparent"]TABLE[/TD]
[TD="class: xl60877, bgcolor: transparent"] [/TD]
[TD="class: xl60877, bgcolor: transparent"] [/TD]
[TD="class: xl60877, bgcolor: transparent"] [/TD]
[TD="class: xl60877, bgcolor: transparent"] [/TD]
[TD="class: xl60877, bgcolor: transparent"] [/TD]
[TD="class: xl60878, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl60879, width: 260, bgcolor: transparent"]Payment Date[/TD]
[TD="class: xl60870, width: 71, bgcolor: transparent"]Ref[/TD]
[TD="class: xl60888, width: 100, bgcolor: transparent"]Beginning Balance[/TD]
[TD="class: xl60888, width: 67, bgcolor: transparent"]Payment[/TD]
[TD="class: xl60888, width: 64, bgcolor: transparent"]Principal[/TD]
[TD="class: xl60888, width: 73, bgcolor: transparent"]Interest[/TD]
[TD="class: xl60889, width: 82, bgcolor: transparent"]Ending Balance[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]30/11/2016[/TD]
[TD="class: xl60881, bgcolor: transparent"] A12 [/TD]
[TD="class: xl60871, bgcolor: transparent"]900,000.00[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]62,558.69[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]8,580.82[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]837,441.31[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]28/02/2017[/TD]
[TD="class: xl60881, bgcolor: transparent"] A13 [/TD]
[TD="class: xl60871, bgcolor: transparent"]837,441.31[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]58,749.97[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]12,389.54[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]778,691.34[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]31/05/2017[/TD]
[TD="class: xl60881, bgcolor: transparent"] A14 [/TD]
[TD="class: xl60871, bgcolor: transparent"]1,588,691.34[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]47,246.42[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]23,893.09[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,541,444.92[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]30/09/2017[/TD]
[TD="class: xl60881, bgcolor: transparent"] A15 [/TD]
[TD="class: xl60871, bgcolor: transparent"]1,631,444.92[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]39,116.56[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]32,022.95[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,592,328.36[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]30/11/2017[/TD]
[TD="class: xl60881, bgcolor: transparent"] A16 [/TD]
[TD="class: xl60871, bgcolor: transparent"]1,592,328.36[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]55,172.60[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]15,966.91[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,537,155.76[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]28/02/2018[/TD]
[TD="class: xl60881, bgcolor: transparent"] A17 [/TD]
[TD="class: xl60871, bgcolor: transparent"]1,537,155.76[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]48,398.03[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]22,741.48[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,488,757.73[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]31/05/2018[/TD]
[TD="class: xl60881, bgcolor: transparent"] A18 [/TD]
[TD="class: xl60871, bgcolor: transparent"]1,488,757.73[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]48,624.60[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]22,514.91[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,440,133.13[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]30/09/2018[/TD]
[TD="bgcolor: transparent"]A19[/TD]
[TD="class: xl60871, bgcolor: transparent"]1,440,133.13[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.50[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]42,257.93[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]28,881.57[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,397,875.20[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]30/11/2018[/TD]
[TD="bgcolor: transparent"]A20[/TD]
[TD="class: xl60871, bgcolor: transparent"]1,397,875.20[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]57,122.46[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]14,017.05[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,340,752.74[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]28/02/2019[/TD]
[TD="bgcolor: transparent"]A21[/TD]
[TD="class: xl60871, bgcolor: transparent"]1,340,752.74[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.50[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]51,303.71[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]19,835.79[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,289,449.03[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]31/05/2019[/TD]
[TD="bgcolor: transparent"]A22[/TD]
[TD="class: xl60871, bgcolor: transparent"]1,289,449.03[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]51,638.80[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]19,500.71[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,237,810.23[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]30/09/2019[/TD]
[TD="bgcolor: transparent"]A23[/TD]
[TD="class: xl60871, bgcolor: transparent"]1,237,810.23[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]46,315.48[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]24,824.03[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,191,494.75[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]30/11/2019[/TD]
[TD="bgcolor: transparent"]A24[/TD]
[TD="class: xl60871, bgcolor: transparent"]1,191,494.75[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]59,191.92[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]11,947.59[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,132,302.83[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]29/02/2020[/TD]
[TD="bgcolor: transparent"]A25[/TD]
[TD="class: xl60871, bgcolor: transparent"]1,132,302.83[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]54,201.50[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]16,938.01[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,078,101.33[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]31/05/2020[/TD]
[TD="bgcolor: transparent"]A26[/TD]
[TD="class: xl60871, bgcolor: transparent"]1,078,101.33[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]54,835.07[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]16,304.44[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]1,023,266.26[/TD]
[/TR]
[TR]
[TD="class: xl60880, bgcolor: transparent"]30/09/2020[/TD]
[TD="bgcolor: transparent"]A27[/TD]
[TD="class: xl60871, bgcolor: transparent"]1,023,266.26[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]71,139.50[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]50,618.11[/TD]
[TD="class: xl60869, bgcolor: transparent, align: right"]20,521.39[/TD]
[TD="class: xl60882, bgcolor: transparent, align: right"]972,648.15[/TD]
[/TR]
[TR]
[TD="class: xl60883, bgcolor: transparent"]30/11/2020[/TD]
[TD="class: xl60884, bgcolor: transparent"]A28[/TD]
[TD="class: xl60885, bgcolor: transparent"]972,648.15[/TD]
[TD="class: xl60886, bgcolor: transparent, align: right"]71,139.51[/TD]
[TD="class: xl60886, bgcolor: transparent, align: right"]61,386.38[/TD]
[TD="class: xl60886, bgcolor: transparent, align: right"]9,753.13[/TD]
[TD="class: xl60887, bgcolor: transparent, align: right"]911,261.77
[/TD]
[/TR]
</tbody>[/TABLE]
 
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.
 
Upvote 0
Thanks so much Rada. This helps me and thanks for quick response too. Need to learn this stuff.
 
Upvote 0
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. :)
 
Upvote 0
Thanks again Rada. Your help has been invaluable to solve a problem quickly too. Thankgoodness for this great community too.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top