lookup return from range of dates

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

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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
cell E2 date is July 30, 2020.
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

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

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