Find Missing Day of Month

gguardia · May 6, 2021

Column D has a list of the dates the market was open, column K has the market close on that date. I would like to find the value of the market close on, say, every 14th of the month. However, if the market is closed on the 14th (weekend, holiday, the week following 9/11, for example), I want the the value of the market close on the next day the market is open.

Example: 1/14/1928 was a Saturday, so I want the value on the next open day, which was Monday, 1/16/1928.
Example: 7/14/1977, a Thursday, the market was closed, but was opened on Friday 7/15/1977, so I want the value on that Friday.
Example: 9/14/2001 the market was closed. The next day the market was open was 9/17/2001, so I would like the value on that day.

How can I do this using formulas only, without using VBA?

Momentman · May 6, 2021

I am not sure what version you are using, but if one with XLOOKUP then maybe

iqcorr.xlsx

A

K

L

M

N

1

Date

Mkt Close

Date

14-Sep-01

2

01-Sep-01

₦116.00

Mkt Close

₦126.00

3

03-Sep-01

₦107.00

4

04-Sep-01

₦125.00

5

05-Sep-01

₦137.00

6

06-Sep-01

₦103.00

7

07-Sep-01

₦130.00

8

10-Sep-01

₦128.00

9

11-Sep-01

₦120.00

10

12-Sep-01

₦112.00

11

13-Sep-01

₦127.00

12

17-Sep-01

₦126.00

13

18-Sep-01

₦134.00

14

19-Sep-01

₦113.00

15

20-Sep-01

₦128.00

16

21-Sep-01

₦127.00

17

24-Sep-01

₦114.00

18

25-Sep-01

₦120.00

19

26-Sep-01

₦103.00

20

27-Sep-01

₦109.00

21

28-Sep-01

₦139.00

Sheet4

Cell Formulas
Range	Formula
N2	N2	=XLOOKUP(N1,$A$2:$A$21,$K$2:$K$21,,1)

gguardia · May 7, 2021

Thank you, Momentman, that is exactly what I was looking for! And I now know a new function I was not aware of.

gguardia · May 9, 2021

Ok, it was working perfectly the first day. When I reopened the spreadsheet the following day, the results are all for the same day, over a year in advance. I can not figure out why it is not working!

S&P500 Daily Data.xlsx

A

B

C

D

E

F

G

H

J

K

L

M

N

1

Date

Open

High

Low

Close*

Adj Close**

Volume

Day of month:

14

2

Jan 03, 1928

17.76

3

Jan 04, 1928

17.72

-

01/14/1928

Apr 01, 1929

24.88

4

Jan 05, 1928

17.55

-

02/14/1928

Apr 01, 1929

24.88

5

Jan 06, 1928

17.66

-

03/14/1928

Apr 01, 1929

24.88

6

Jan 09, 1928

17.5

-

04/14/1928

Apr 01, 1929

24.88

7

Jan 10, 1928

17.37

-

8

Jan 11, 1928

17.35

-

9

Jan 12, 1928

17.47

-

10

Jan 13, 1928

17.58

-

11

Jan 16, 1928

17.29

-

12

Jan 17, 1928

17.3

-

13

Jan 18, 1928

17.26

-

14

Jan 19, 1928

17.38

-

15

Jan 20, 1928

17.48

-

16

Jan 23, 1928

17.64

-

17

Jan 24, 1928

17.71

-

18

Jan 25, 1928

17.52

-

19

Jan 26, 1928

17.63

-

20

Jan 27, 1928

17.69

-

21

Jan 30, 1928

17.49

-

22

Jan 31, 1928

17.57

-

23

Feb 01, 1928

17.53

-

24

Feb 02, 1928

17.63

-

25

Feb 03, 1928

17.4

-

26

Feb 06, 1928

17.45

-

27

Feb 07, 1928

17.44

-

28

Feb 08, 1928

17.49

-

29

Feb 09, 1928

17.55

-

30

Feb 10, 1928

17.54

-

31

Feb 14, 1928

17.44

-

32

Feb 15, 1928

17.4

-

33

Feb 16, 1928

17.35

-

34

Feb 17, 1928

17.04

-

35

Feb 20, 1928

16.95

-

36

Feb 21, 1928

17.11

-

37

Feb 23, 1928

17.13

-

38

Feb 24, 1928

17.18

-

39

Feb 27, 1928

17.11

-

40

Feb 28, 1928

17.16

-

41

Feb 29, 1928

17.26

-

42

Mar 01, 1928

17.3

-

43

Mar 02, 1928

17.3

-

44

Mar 05, 1928

17.57

-

45

Mar 06, 1928

17.67

-

46

Mar 07, 1928

17.58

-

47

Mar 08, 1928

17.64

-

48

Mar 09, 1928

17.93

-

49

Mar 12, 1928

18

-

50

Mar 13, 1928

17.92

-

51

Mar 14, 1928

17.93

-

52

Mar 15, 1928

18.07

-

53

Mar 16, 1928

18.26

-

54

Mar 19, 1928

18.36

-

55

Mar 20, 1928

18.46

-

56

Mar 21, 1928

18.65

-

57

Mar 22, 1928

18.6

-

58

Mar 23, 1928

18.78

-

59

Mar 26, 1928

19.05

-

60

Mar 27, 1928

18.94

-

61

Mar 28, 1928

18.89

-

62

Mar 29, 1928

18.95

-

63

Mar 30, 1928

19.28

-

64

Apr 02, 1928

18.91

-

65

Apr 03, 1928

19.02

-

66

Apr 04, 1928

18.99

-

67

Apr 05, 1928

19.23

-

68

Apr 09, 1928

19.27

-

69

Apr 10, 1928

19.1

-

70

Apr 11, 1928

19.44

-

71

Apr 12, 1928

19.41

-

72

Apr 13, 1928

19.73

-

73

Apr 16, 1928

19.68

-

74

Apr 17, 1928

19.52

-

75

Apr 18, 1928

19.52

-

76

Apr 19, 1928

19.63

-

77

Apr 20, 1928

19.34

-

78

Apr 23, 1928

19.15

-

79

Apr 24, 1928

19.25

-

80

Apr 25, 1928

19.33

-

81

Apr 26, 1928

19.47

-

82

Apr 27, 1928

19.68

-

83

Apr 30, 1928

19.75

-

84

May 01, 1928

19.78

-

S&P500Data

Cell Formulas
Range	Formula
L3	L3	=DATE(1928,1,$M$1)
M3	M3	=XLOOKUP($L3,$A:$A,$A:$A,,1)
N3:N6	N3	=XLOOKUP($L3,$A:$A,$F:$F,,1)
L4:L6	L4	=EDATE(L3,1)
M4:M6	M4	=XLOOKUP($L4,$A:$A,$A:$A,,1,1)