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

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

Pivot Table Drill Down

Click here to reveal answer

Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Sort by date Sort by votes

Asbestos_Jen

Active Member

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

S

Question

AVERAGEIF TROUBLES

Replies: 3

Views: 79

Nov 13, 2024

AhoyNC

A

A

Solved

Select current month in combobox on userform for English language

2 3

Replies: 29

Views: 319

Dec 13, 2024

Ali M

A

J

Question

auto filling cells based on preset format

Replies: 1

Views: 35

Dec 2, 2024

HighAndWilder

H

Solved

Conditional Formatting last n rows as data entered

Replies: 6

Views: 362

Apr 29, 2024

Fluff

O

Question

Graph to view comparison of monthly changes to numbers

Replies: 0

Views: 164

Jul 5, 2024

OJMcGee

O

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,224,813

Messages: 6,181,111

Members: 453,021

Latest member: Justyna P

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top