lookup date range or one earlier

D

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]
 
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

G
  • Question Question
sum a date range
Replies
2
Views
657
Gealer
G
D
  • Question Question
Help with linking events in one table to specific location in another table
Replies
1
Views
493
RasGhul
RasGhul
K
  • Question Question
Add/Update Data from One Tab to Another
Replies
0
Views
431
kabijenn
K
D
  • Question Question
Find interest for the last financial year in an offset column and indirect reference
Replies
0
Views
391
Dares2
D
R
  • Question Question
Multiple lookup
Replies
2
Views
483
robrobby
R

Share this page

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