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 7, 2020

Hey PJ, for presentation, I am reversing the date order (column b) from newest to oldest -- to --- oldest to newest. This affects your formula for the close under AND close over.

Or, I can change the counter so you don't have to do redo that formula. It's currently in Column G a dn it's . . . =IF(LARGE(C5:C11,1)<=C5,1,1+G4) for newest dates to oldest dates. If I change it from oldest date to newest dates, how does that change this counter formula? I can't get it to work.

Thanks!

matthewlouis · Jul 7, 2020

Here is what it looks like . . . 88.24, the first price showing it closed over 87.85 . . . but . . . the correct number should 88.03 on 2/20/70.

Again, I reversed the dates . . . and that affected your formula
=IFERROR(INDEX($E$4:$E$13000,LARGE(IF(($A$4:$A$13000>=J30)*($A$4:$A$13000<=L30)*($E$4:$E$13000>I30),ROW($E$4:$E$13000)-ROW($E$3)),1)),"")

Just a reference in case by prior post doesn't make sense.

matthewlouis · Jul 7, 2020

Well, I think I figured it out . . .

=IFERROR(INDEX($E$4:$E$13000,LARGE(IF(($A$4:$A$13000>=J30)*($A$4:$A$13000<=L30)*($E$4:$E$13000>I30),ROW($E$4:$E$13000)-ROW($E$3)),COUNT(IF(($A$4:$A$13000>=J30)*($A$4:$A$13000<=L30)*($E$4:$E$13000>I30),ROW($E$4:$E$13000)-ROW($E$3))))),"")

This works! Sorry to bother you!

pjmorris · Jul 8, 2020

Hi Matthew,

Brilliant that you've managed to make it work. I note though that to do so you've replaced the 1 in your formula at post #32 with the COUNT section
I think that because you've reversed the order you can change that formula simply by changing Large to Small.

You might also consider using Jason's formula:

=IFERROR(INDEX(F:F,AGGREGATE(14,6,ROW($E$4:$E$13000)/($A$4:$A$13000>=J30)/($A$4:$A$13000<=L30)/($E$4:$E$13000>I30),1)) ,"") (PS. I've not been able to try this!)

HTH and congrats on getting yours to work - I think we've both learnt something. Many thanks

matthewlouis · Jul 10, 2020

Hey pj, I have one more small project using the same data . . . can't seem to make it work. Are you up for it? Wanted to ask first . . .

pjmorris · Jul 10, 2020

Of course, but the best course of action is to create a new Thread - its easier for others to engage with - and the wider your audience the more likely you are to have success.

matthewlouis · Jul 10, 2020

OK, thanks. I will try to use the same data since you should recall it better. Maybe tomorrow, please watch for it, I enjoy working with you!

pjmorris · Jul 10, 2020

Not sure what time I'll be looking - taking Grandson dinghy sailing!

matthewlouis · Jul 10, 2020

Haha, have my grandson tomorrow as well. And granddaughter.... resting up!

matthewlouis · Jul 11, 2020

Will try and new post using the new XLBBB

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

Daily SPH 2016

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T4:U4	Cell Value	=41229	text	YES
T4:U4	Cell Value	=41166	text	YES
T4:U4	Cell Value	=41064	text	YES
T4:U4	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

Index and Match between Two Dates

matthewlouis

Active Member

matthewlouis

Active Member

Excel Facts

matthewlouis

Active Member

Attachments

matthewlouis

Active Member

pjmorris

Well-known Member

matthewlouis

Active Member

pjmorris

Well-known Member

matthewlouis

Active Member

pjmorris

Well-known Member

matthewlouis

Active Member

matthewlouis

Active Member

Similar threads

Forum statistics

Share this page

Index and Match between Two Dates

Active Member

Active Member

Excel Facts

Active Member

Attachments

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

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