lookup return from range of dates

S

savvysum

New Member
Joined
Jun 22, 2022
Messages
17
Office Version
  1. 365
Platform
  1. MacOS
I need to lookup & return an interest rate between date ranges.

Any date between June 1, 2020 - March 31, 2022; return 2.00%
Any date between April 1, 2022 - June 30, 2023; return 3.00%

data table
2.00%6/1/20203/31/2022
3.00%4/1/20226/30/2023
3.92%7/1/202312/31/2023
5.33%1/1/202412/31/2024

here is the excel sheet w. the data
docs.google.com

Mr Excel

lookup range of dates 2.00%,6/1/2020,3/31/2022,date,int rate 3.00%,4/1/2022,6/30/2023,Jul-20,2.00% 3.92%,7/1/2023,12/31/2023,Aug-20,2.00% 5.33%,1/1/2024,12/31/2024,Sep-20,2.00% Oct-20,2.00% Nov-20,2.00% Dec-20,2.00% Jan-21,2.00% Feb-21,2.00% Mar-21,2.00% Apr-21,2.00% May-21,2.00% Jun-21,2.00% Ju...
docs.google.com docs.google.com

cell E2 date is July 30, 2020.
cell F2 return 2.00%
dateint rate
Jul-202.00%
Aug-202.00%
Sep-202.00%
Oct-202.00%
Nov-202.00%
Dec-202.00%
Jan-212.00%
Feb-212.00%
Mar-212.00%
Apr-212.00%
May-212.00%
Jun-212.00%
Jul-212.00%
Aug-212.00%
Sep-212.00%
Oct-212.00%
Nov-212.00%
Dec-212.00%
Jan-222.00%
Feb-222.00%
Mar-222.00%
Apr-223.00%
May-223.00%
Jun-223.00%
Jul-223.00%
Aug-223.00%
Sep-223.00%
Oct-223.00%
Nov-223.00%
Dec-223.00%
Jan-233.00%
Feb-233.00%
Mar-233.00%
Apr-233.00%
May-233.00%
Jun-233.00%
Jul-233.92%
Aug-233.92%
Sep-233.92%
Oct-233.92%
Nov-233.92%
Dec-233.92%
Jan-245.33%
Feb-245.33%
Mar-245.33%
Apr-245.33%
May-245.33%
Jun-245.33%
Jul-245.33%
Aug-245.33%
Sep-245.33%
Oct-245.33%
Nov-245.33%
Dec-245.33%
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Try this. I used "early" as the message for dates before the minimum and "later" for dates after the maximum.
Book2
ABCDEF
12.0%1-Jun-2031-Mar-22dateint rate
23.0%1-Apr-2230-Jun-231-Jul-202.0%
33.9%1-Jul-2331-Dec-231-Aug-202.0%
45.3%1-Jan-2431-Dec-241-Sep-202.0%
51-Oct-202.0%
61-Nov-202.0%
71-Dec-202.0%
81-Jan-212.0%
91-Feb-212.0%
101-Mar-212.0%
111-Apr-212.0%
121-May-212.0%
131-Jun-212.0%
141-Jul-212.0%
151-Aug-212.0%
161-Sep-212.0%
171-Oct-212.0%
181-Nov-212.0%
191-Dec-212.0%
201-Jan-222.0%
211-Feb-222.0%
221-Mar-222.0%
231-Apr-223.0%
241-May-223.0%
251-Jun-223.0%
261-Jul-223.0%
271-Aug-223.0%
281-Sep-223.0%
291-Oct-223.0%
301-Nov-223.0%
311-Dec-223.0%
321-Jan-233.0%
331-Feb-233.0%
341-Mar-233.0%
351-Apr-233.0%
361-May-233.0%
371-Jun-233.0%
381-Jul-233.9%
391-Aug-233.9%
401-Sep-233.9%
411-Oct-233.9%
421-Nov-233.9%
431-Dec-233.9%
441-Jan-245.3%
451-Feb-245.3%
461-Mar-245.3%
471-Apr-245.3%
481-May-245.3%
491-Jun-245.3%
501-Jul-245.3%
511-Aug-245.3%
521-Sep-245.3%
531-Oct-245.3%
541-Nov-245.3%
551-Dec-245.3%
Sheet1
Cell Formulas
RangeFormula
F2:F55F2=IF(E2<$B$1,"early",IF(E2<$C$1,$A$1,IF(E2<$C$2,$A$2,IF(E2<$C$3,$A$3,IF(E2<$C$4,$A$4,"later")))))
 
Upvote 0
savvysum said:
cell E2 date is July 30, 2020.
Click to expand...
Not that important but in your workbook it appears to actually be July 1, 2020

I have assumed all dates in col E lie within the date ranges in columns A:C as per the sample data.

savvysum Mr Excel.xlsx
ABCDEF
12.00%1/06/202031/03/2022dateint rate
23.00%1/04/202230/06/20231/07/20202.00%
33.92%1/07/202331/12/20231/08/20202.00%
45.33%1/01/202431/12/20241/09/20202.00%
51/10/20202.00%
61/11/20202.00%
71/12/20202.00%
81/01/20212.00%
91/02/20212.00%
101/03/20212.00%
111/04/20212.00%
121/05/20212.00%
131/06/20212.00%
141/07/20212.00%
151/08/20212.00%
161/09/20212.00%
171/10/20212.00%
181/11/20212.00%
191/12/20212.00%
201/01/20222.00%
211/02/20222.00%
221/03/20222.00%
231/04/20223.00%
241/05/20223.00%
251/06/20223.00%
261/07/20223.00%
271/08/20223.00%
281/09/20223.00%
291/10/20223.00%
301/11/20223.00%
311/12/20223.00%
321/01/20233.00%
331/02/20233.00%
341/03/20233.00%
351/04/20233.00%
361/05/20233.00%
371/06/20233.00%
381/07/20233.92%
391/08/20233.92%
401/09/20233.92%
411/10/20233.92%
421/11/20233.92%
431/12/20233.92%
441/01/20245.33%
451/02/20245.33%
461/03/20245.33%
471/04/20245.33%
481/05/20245.33%
491/06/20245.33%
501/07/20245.33%
511/08/20245.33%
521/09/20245.33%
531/10/20245.33%
541/11/20245.33%
551/12/20245.33%
lookup range of dates
Cell Formulas
RangeFormula
F2:F55F2=XLOOKUP(E2,B$1:B$4,A$1:A$4,,-1)
 
Upvote 0
Solution
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Week Number
Replies
2
Views
127
vmjan02
V
vinzent
  • Question
Formula to get the 4th workday before end of month
Replies
8
Views
170
Tetra201
Tetra201
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
266
amolvijay
A
M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
247
Peter_SSs
Peter_SSs
B
  • Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
198
B-radK
B

Forum statistics

Threads
1,215,212
Messages
6,123,655
Members
449,113
Latest member
Hochanz

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