Index and Match between Two Dates

matthewlouis · Jul 3, 2020

Hey EXCEL experts, I need a formula for an index and match that looks for a number that is below another cell number. Attached is an image with more explanation. The key is to keep the index / match between the two dates on the sheet in each column.

Thanks in advance!

matthewlouis · Jul 11, 2020

Will you look at this and see if you can use this . . . I don't have any formulas in it, just the results of what each cell is supposed to be. Don't know why it looks so BIG on the screen, how do I make it small and more readable? Or, can you open it where you are in Excel? Not sure how XLB22 works.

Need help with the formulas from Columns P to Q. I will make a NEW post with more explanation if this posted OK and you can see it. Thanks!

jasonb75 · Jul 11, 2020

I've not seen that before with XL2BB, best guess would be that you have increased the font size and decreased the zoom in your sheet (old habit from days of lower screen resolutions?). I believe that XL2BB copies the formatting from your sheet but zoom will always be at 100% which is why it looks so big.

I've just tried copying your example to excel and it looks normal when I reduce zoom to 50%. (the way it works is to click the copy icon in the top left corner of the XL2BB table, then paste into an empty excel sheet, it gives us an almost identical copy of your data to work with, including formulas when you have them).

matthewlouis · Jul 11, 2020

Ah, thanks, Jason, I'll do that before I paste in next post.

matthewlouis · Jul 11, 2020

Let's see . . .

matthewlouis · Jul 11, 2020

Checking this . . .

2020 July 11 Data for PJ.xlsb

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

2

Daily SPH

Date

Date Index

High

Low

Close

Daily Volume

Swing Point High Price

Date of Swing Point High

Swing Point High Volume

60 Day Window for "Closed Over" Date

Price Closed Over Swing Point High

Date Price Closed Over Swing Point High

Volume on Date Price Closed Over Swing Point High

6 - Day Window for FIRST Test Date

FIRST TEST - Index Column D to see if price touched the Swing Point High Price <= Column O Date (6 Days)

First Test Date <= 6 Bars

Index Column D - did Price FIRST Test into Swing Point High Price GREATER THAN Column O Date AND LESS THAN Column K Date

First Test Date > 6 Bars

Index Column D - did Price SECOND Test into Swing Point High Price LESS THAN Column K Date

Index Column D - did Price THIRD Test into Swing Point High Price LESS THAN Column K Date

3

4

Daily SPH

9/7/16

-965

2,187.87

2,179.07

2,186.16

3,319,420,000

2,187.87

9/7/2016

3,319,420,000

12/1/2016

2,198.18

11/21/2016

3,607,010,000

11/30/2016

No

2,187.44

12/1/2016

No

5

Daily SPH

9/8/16

-964

2,184.94

2,177.49

2,181.30

3,727,840,000

6

Daily SPH

9/9/16

-963

2,169.08

2,127.81

4,233,960,000

7

Daily SPH

9/12/16

-962

2,163.30

2,119.12

2,159.04

4,010,480,000

8

Daily SPH

9/13/16

-961

2,150.47

2,120.27

2,127.02

4,141,670,000

9

Daily SPH

9/14/16

-960

2,141.33

2,119.90

2,125.77

3,664,100,000

10

Daily SPH

9/15/16

-959

2,151.31

2,122.36

2,147.26

3,373,720,000

11

Daily SPH

9/16/16

-958

2,146.48

2,131.20

2,139.16

5,014,360,000

12

Daily SPH

9/19/16

-957

2,153.61

2,135.91

2,139.12

3,163,000,000

13

Daily SPH

9/20/16

-956

2,150.80

2,139.17

2,139.76

3,140,730,000

14

Daily SPH

9/21/16

-955

2,165.11

2,139.57

2,163.12

3,712,090,000

15

Daily SPH

9/22/16

-954

2,179.99

2,170.94

2,177.18

3,552,830,000

2,179.99

9/22/2016

3,552,830,000

12/16/2016

2,180.39

11/15/2016

4,543,860,000

11/23/2016

2,179.22

11/16/2016

No

16

Daily SPH

9/23/16

-953

2,173.75

2,163.97

2,164.69

3,317,190,000

17

Daily SPH

9/26/16

-952

2,158.54

2,145.04

2,146.10

3,216,170,000

18

Daily SPH

9/27/16

-951

2,161.13

2,141.55

2,159.93

3,437,770,000

19

Daily SPH

9/28/16

-950

2,172.40

2,151.79

2,171.37

3,891,460,000

20

Daily SPH

9/29/16

-949

2,172.67

2,145.20

2,151.13

4,249,220,000

21

Daily SPH

9/30/16

-948

2,175.30

2,156.51

2,168.27

4,173,340,000

2,175.30

9/30/2016

4,173,340,000

12/27/2016

2,180.39

11/15/2016

4,543,860,000

11/23/2016

No

22

Daily SPH

10/3/16

-947

2,164.41

2,154.77

2,161.20

3,137,550,000

23

Daily SPH

10/4/16

-946

2,165.46

2,144.01

2,150.49

3,750,890,000

24

Daily SPH

10/5/16

-945

2,163.95

2,155.15

2,159.73

3,906,550,000

25

Daily SPH

10/6/16

-944

2,162.93

2,150.28

2,160.77

3,461,550,000

26

Daily SPH

10/7/16

-943

2,165.86

2,144.85

2,153.74

3,619,890,000

27

Daily SPH

10/10/16

-942

2,169.60

2,160.39

2,163.66

2,916,550,000

2,169.60

10/10/2016

2,916,550,000

1/5/2017

2,180.39

11/15/2016

4,543,860,000

11/23/2016

28

Daily SPH

10/11/16

-941

2,161.56

2,128.84

2,136.73

3,438,270,000

29

Daily SPH

10/12/16

-940

2,145.36

2,132.77

2,139.18

2,977,100,000

30

Daily SPH

10/13/16

-939

2,138.19

2,114.72

2,132.55

3,580,450,000

31

Daily SPH

10/14/16

-938

2,149.19

2,132.98

3,228,150,000

32

Daily SPH

10/17/16

-937

2,135.61

2,124.43

2,126.50

2,830,390,000

33

Daily SPH

10/18/16

-936

2,144.38

2,135.49

2,139.60

3,170,000,000

34

Daily SPH

10/19/16

-935

2,148.44

2,138.15

2,144.29

3,362,670,000

35

Daily SPH

10/20/16

-934

2,147.18

2,133.44

2,141.34

3,337,170,000

36

Daily SPH

10/21/16

-933

2,142.63

2,130.09

2,141.16

3,448,850,000

37

Daily SPH

10/24/16

-932

2,154.79

2,146.91

2,151.33

3,357,320,000

2,154.79

10/24/2016

3,357,320,000

1/20/2017

2,163.26

11/9/2016

6,264,150,000

11/17/2016

38

Daily SPH

10/25/16

-931

2,151.44

2,141.93

2,143.16

3,751,340,000

39

Daily SPH

10/26/16

-930

2,145.73

2,131.59

2,139.43

3,775,200,000

40

Daily SPH

10/27/16

-929

2,147.13

2,132.52

2,133.04

4,204,830,000

41

Daily SPH

10/28/16

-928

2,140.72

2,119.36

2,126.41

4,019,510,000

42

Daily SPH

10/31/16

-927

2,133.25

2,125.53

2,126.15

3,922,400,000

43

Daily SPH

11/1/16

-926

2,131.45

2,097.85

2,111.72

4,532,160,000

44

Daily SPH

11/2/16

-925

2,111.76

2,094.00

2,097.94

4,248,580,000

45

Daily SPH

11/3/16

-924

2,102.56

2,085.23

2,088.66

3,886,740,000

46

Daily SPH

11/4/16

-923

2,099.07

2,083.79

2,085.18

3,837,860,000

47

Daily SPH

11/7/16

-922

2,132.00

2,100.59

2,131.52

3,736,060,000

48

Daily SPH

11/8/16

-921

2,146.87

2,123.56

2,139.56

3,916,930,000

49

Daily SPH

11/9/16

-920

2,170.10

2,125.35

2,163.26

6,264,150,000

50

Daily SPH

11/10/16

-919

2,182.30

2,151.17

2,167.48

6,451,640,000

51

Daily SPH

11/11/16

-918

2,165.92

2,152.49

2,164.45

4,988,050,000

52

Daily SPH

11/14/16

-917

2,171.36

2,156.08

2,164.20

5,367,200,000

53

Daily SPH

11/15/16

-916

2,180.84

2,166.38

2,180.39

4,543,860,000

54

Daily SPH

11/16/16

-915

2,179.22

2,172.20

2,176.94

3,830,590,000

55

Daily SPH

11/17/16

-914

2,188.06

2,176.65

2,187.12

3,809,160,000

56

Daily SPH

11/18/16

-913

2,189.89

2,180.38

2,181.90

3,572,400,000

57

Daily SPH

11/21/16

-912

2,198.70

2,186.43

2,198.18

3,607,010,000

58

Daily SPH

11/22/16

-911

2,204.80

2,194.51

2,202.94

3,957,940,000

59

Daily SPH

11/23/16

-910

2,204.72

2,194.51

2,204.72

3,418,640,000

60

Daily SPH

11/25/16

-909

2,213.35

2,206.27

2,213.35

1,584,600,000

61

Daily SPH

11/28/16

-908

2,211.14

2,200.36

2,201.72

3,505,650,000

62

Daily SPH

11/29/16

-907

2,210.46

2,198.15

2,204.66

3,706,560,000

63

Daily SPH

11/30/16

-906

2,214.10

2,198.81

5,533,980,000

64

Daily SPH

12/1/16

-905

2,202.60

2,187.44

2,191.08

5,063,740,000

65

Daily SPH

12/2/16

-904

2,197.95

2,188.37

2,191.95

3,779,500,000

66

Daily SPH

12/5/16

-903

2,209.42

2,199.97

2,204.71

3,895,230,000

67

Daily SPH

12/6/16

-902

2,212.78

2,202.21

2,212.23

3,855,320,000

68

Daily SPH

12/7/16

-901

2,241.63

2,208.93

2,241.35

4,501,820,000

69

Daily SPH

12/8/16

-900

2,251.69

2,237.57

2,246.19

4,200,580,000

70

Daily SPH

12/9/16

-899

2,259.80

2,249.23

2,259.53

3,884,480,000

71

Daily SPH

12/12/16

-898

2,264.03

2,252.37

2,256.96

4,034,510,000

72

Daily SPH

12/13/16

-897

2,277.53

2,263.32

2,271.72

3,857,590,000

2,277.53

12/13/2016

3,857,590,000

3/13/2017

2,280.07

1/24/2017

3,810,960,000

2/1/2017

73

Daily SPH

12/14/16

-896

2,276.20

2,248.44

2,253.28

4,406,970,000

74

Daily SPH

12/15/16

-895

2,272.12

2,253.77

2,262.03

4,168,200,000

75

Daily SPH

12/16/16

-894

2,268.05

2,254.24

2,258.07

5,920,340,000

76

Daily SPH

12/19/16

-893

2,267.47

2,258.21

2,262.53

3,248,370,000

77

Daily SPH

12/20/16

-892

2,272.56

2,266.14

2,270.76

3,298,780,000

78

Daily SPH

12/21/16

-891

2,271.23

2,265.15

2,265.18

2,852,230,000

79

Daily SPH

12/22/16

-890

2,263.18

2,256.08

2,260.96

2,876,320,000

80

Daily SPH

12/23/16

-889

2,263.79

2,258.84

2,263.79

2,020,550,000

81

Daily SPH

12/27/16

-888

2,273.82

2,266.15

2,268.88

1,987,080,000

2,273.82

12/27/2016

1,987,080,000

3/24/2017

2,276.98

1/6/2017

3,339,890,000

1/17/2017

82

Daily SPH

12/28/16

-887

2,271.31

2,249.11

2,249.92

2,392,360,000

83

Daily SPH

12/29/16

-886

2,254.51

2,244.56

2,249.26

2,336,370,000

84

Daily SPH

12/30/16

-885

2,253.58

2,233.62

2,238.83

2,670,900,000

85

Daily SPH

1/3/17

-884

2,263.88

2,245.13

2,257.83

3,770,530,000

86

Daily SPH

1/4/17

-883

2,272.82

2,261.60

2,270.75

3,764,890,000

87

Daily SPH

1/5/17

-882

2,271.50

2,260.45

2,269.00

3,761,820,000

88

Daily SPH

1/6/17

-881

2,282.10

2,264.06

2,276.98

3,339,890,000

2,282.10

1/6/2017

3,339,890,000

4/4/2017

2,298.37

1/25/2017

3,846,020,000

2/2/2017

Daily SPH 2016

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T4:V4	Cell Value	=41229	text	YES
T4:V4	Cell Value	=41166	text	YES
T4:V4	Cell Value	=41064	text	YES
T4:V4	Cell Value	=41229	text	YES
O4:S4,K4:N965	Cell Value	=41229	text	YES
O4:S4,K4:N965	Cell Value	=41166	text	YES
O4:S4,K4:N965	Cell Value	=41064	text	YES
K4:K965,H4:I965,O4:S4,M4:N965	Cell Value	=41229	text	YES

matthewlouis · Jul 11, 2020

That's better, thanks, Jason!

Index and Match between Two Dates

matthewlouis

Active Member

matthewlouis

Active Member

Excel Facts

jasonb75

Well-known Member

matthewlouis

Active Member

matthewlouis

Active Member

matthewlouis

Active Member

matthewlouis

Active Member

Similar threads

Forum statistics

Share this page

Index and Match between Two Dates

matthewlouis

Active Member

matthewlouis

Active Member

Excel Facts

jasonb75

Well-known Member

matthewlouis

Active Member

matthewlouis

Active Member

matthewlouis

Active Member

matthewlouis

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock