If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

lookup return from range of dates

Thread starter savvysum
Start date Apr 2, 2024
Tags

filter index lookup

S

savvysum

New Member

Apr 2, 2024

#1

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/2020	3/31/2022
3.00%	4/1/2022	6/30/2023
3.92%	7/1/2023	12/31/2023
5.33%	1/1/2024	12/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,<a href="https://www.mrexcel.com/board/threads/lookup-return-from-range-of-dates.1256634/#post-6171377">https://www.mrexcel.com/board/threads/lookup-return-from-range-of-dates.1256634/#post-6171377</a> 3.00%,4/1/2022,6/30/2023,Jul-20,2...

docs.google.com

docs.google.com

cell E2 date is July 30, 2020.
cell F2 return 2.00%

date	int rate
Jul-20	2.00%
Aug-20	2.00%
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%
Jul-21	2.00%
Aug-21	2.00%
Sep-21	2.00%
Oct-21	2.00%
Nov-21	2.00%
Dec-21	2.00%
Jan-22	2.00%
Feb-22	2.00%
Mar-22	2.00%
Apr-22	3.00%
May-22	3.00%
Jun-22	3.00%
Jul-22	3.00%
Aug-22	3.00%
Sep-22	3.00%
Oct-22	3.00%
Nov-22	3.00%
Dec-22	3.00%
Jan-23	3.00%
Feb-23	3.00%
Mar-23	3.00%
Apr-23	3.00%
May-23	3.00%
Jun-23	3.00%
Jul-23	3.92%
Aug-23	3.92%
Sep-23	3.92%
Oct-23	3.92%
Nov-23	3.92%
Dec-23	3.92%
Jan-24	5.33%
Feb-24	5.33%
Mar-24	5.33%
Apr-24	5.33%
May-24	5.33%
Jun-24	5.33%
Jul-24	5.33%
Aug-24	5.33%
Sep-24	5.33%
Oct-24	5.33%
Nov-24	5.33%
Dec-24	5.33%

Excel Facts

Last used cell?

Click here to reveal answer

Press Ctrl+End to move to what Excel thinks is the last used cell.

Sort by date Sort by votes

Asbestos_Jen

Board Regular

Apr 2, 2024

#2

Try this. I used "early" as the message for dates before the minimum and "later" for dates after the maximum.

Book2

A

B

C

D

E

F

1

2.0%

1-Jun-20

31-Mar-22

date

int rate

2

3.0%

1-Apr-22

30-Jun-23

1-Jul-20

3

3.9%

1-Jul-23

31-Dec-23

1-Aug-20

4

5.3%

1-Jan-24

31-Dec-24

1-Sep-20

5

1-Oct-20

6

1-Nov-20

7

1-Dec-20

8

1-Jan-21

9

1-Feb-21

10

1-Mar-21

11

1-Apr-21

12

1-May-21

13

1-Jun-21

14

1-Jul-21

15

1-Aug-21

16

1-Sep-21

17

1-Oct-21

18

1-Nov-21

19

1-Dec-21

20

1-Jan-22

21

1-Feb-22

22

1-Mar-22

23

1-Apr-22

24

1-May-22

25

1-Jun-22

26

1-Jul-22

27

1-Aug-22

28

1-Sep-22

29

1-Oct-22

30

1-Nov-22

31

1-Dec-22

32

1-Jan-23

33

1-Feb-23

34

1-Mar-23

35

1-Apr-23

36

1-May-23

37

1-Jun-23

38

1-Jul-23

39

1-Aug-23

40

1-Sep-23

41

1-Oct-23

42

1-Nov-23

43

1-Dec-23

44

1-Jan-24

45

1-Feb-24

46

1-Mar-24

47

1-Apr-24

48

1-May-24

49

1-Jun-24

50

1-Jul-24

51

1-Aug-24

52

1-Sep-24

53

1-Oct-24

54

1-Nov-24

55

1-Dec-24

Sheet1

Cell Formulas
Range	Formula
F2:F55	F2	=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

Peter_SSs

MrExcel MVP, Moderator

Apr 2, 2024

#3

savvysum said:
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

A

B

C

D

E

F

1

2.00%

1/06/2020

31/03/2022

date

int rate

2

3.00%

1/04/2022

30/06/2023

1/07/2020

3

3.92%

1/07/2023

31/12/2023

1/08/2020

4

5.33%

1/01/2024

31/12/2024

1/09/2020

5

1/10/2020

6

1/11/2020

7

1/12/2020

8

1/01/2021

9

1/02/2021

10

1/03/2021

11

1/04/2021

12

1/05/2021

13

1/06/2021

14

1/07/2021

15

1/08/2021

16

1/09/2021

17

1/10/2021

18

1/11/2021

19

1/12/2021

20

1/01/2022

21

1/02/2022

22

1/03/2022

23

1/04/2022

24

1/05/2022

25

1/06/2022

26

1/07/2022

27

1/08/2022

28

1/09/2022

29

1/10/2022

30

1/11/2022

31

1/12/2022

32

1/01/2023

33

1/02/2023

34

1/03/2023

35

1/04/2023

36

1/05/2023

37

1/06/2023

38

1/07/2023

39

1/08/2023

40

1/09/2023

41

1/10/2023

42

1/11/2023

43

1/12/2023

44

1/01/2024

45

1/02/2024

46

1/03/2024

47

1/04/2024

48

1/05/2024

49

1/06/2024

50

1/07/2024

51

1/08/2024

52

1/09/2024

53

1/10/2024

54

1/11/2024

55

1/12/2024

lookup range of dates

Cell Formulas
Range	Formula
F2:F55	F2	=XLOOKUP(E2,B$1:B$4,A$1:A$4,,-1)

Upvote 0

Solution

S

savvysum

New Member

Apr 2, 2024

#4

=XLOOKUP(E2,B$1:B$4,A$1:A$4,,-1)

great formula, thanks!

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Apr 2, 2024

#5

You're welcome. Thanks for the follow-up.

Upvote 0

You must log in or register to reply here.

Similar threads

Solved

Conditional Formatting last n rows as data entered

Replies: 6

Views: 137

Apr 29, 2024

Fluff

J

Question

adding cells based on criteria across several columns

Replies: 5

Views: 220

Apr 23, 2024

Cubist

V

Solved

Week Number

Replies: 2

Views: 174

Mar 19, 2024

vmjan02

V

Question

Formula to get the 4th workday before end of month

Replies: 8

Views: 249

Apr 2, 2024

Tetra201

A

Question

Assign Month number 1 to n based on sequential week range

Replies: 6

Views: 318

Feb 19, 2024

amolvijay

A

Share:

Facebook X (Twitter) Reddit Pinterest Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,216,500

Messages: 6,131,015

Members: 449,615

Latest member: Nic0la

Share this page

Facebook X (Twitter) Reddit Pinterest Tumblr WhatsApp Email Link

Top