Spreadsheet: 743838d1628769066-index-and-match-lookup-to-find-the-last-number-over-50-pleasehelp2
Sheet: Sheet1

 |  | 0
 | 2007-06-30 00:00:00 | 55.6 |  |  |  | Manufacturing PMI |  |  | Score
 | 2007-07-31 00:00:00 | 54.9 |  |  |  | >50 and growing |  |  | [NAME] to +8
 | 2007-08-31 00:00:00 | 54.3 |  |  |  | >50 and slowing |  |  | 0 to +5
 | 2007-09-30 00:00:00 | 53.2 |  |  |  | <50 and slowing |  |  | -5 to -8
 | 2007-10-31 00:00:00 | 51.5 |  |  |  | <50 and growing |  |  | 0 to -5
 | 2007-11-30 00:00:00 | 52.8 |  |  |  | Peak >50,slowing for 1st time |  |  | -8 to -10
 | 2007-12-31 00:00:00 | 52.6 |  |  |  | Trough <50,growing for 1st time |  |  | +8 to +10
 | 2008-01-31 00:00:00 | 52.8
 | 2008-02-29 00:00:00 | 52.3
 | 2008-03-31 00:00:00 | 52 |  |  |  |  | I NEED TO CHECK ROW C AND D, SO I AM LOOKING TO SCORE THE 2 YELLOW HIGHLIGHTED PARTS OF THIS SCORE CARD.  AS A EXAMPLE LETS LOOK AT A1 YELLOW. SO MY THINKING IS THAT IF I HAVE A FORMLARE TO CHECK COLUM C BACK UP TO WHEN THE NUMBER GOES OVER 50, THEN FROM THAT PIONT CHECKS ROW D DOWN TO SEE HOW MANY VALUES UNDER 0 THERE ARE. IF I CAN GET A TURE OR FALSE i CAN USE ANOTHER FORMLAUR TO SCROE IT FROM THE SCORE CARD SO i DON'T NEED THAT PART, i JUST NEED THE PARTS IN YELLEW. i COULD HAVE ONE FORMULAR THAT CHECK A1 IN E AND ANOTHER THAT CHECKS A2 IN F WOULD BE A GREAT SOLUTION. i HVE BEEN WORKING ON THIS FOR SOME TIME TO TRY AND CRACK IT AND COME CLOSE BUT TO BUILD ON THAT WOULD MEAN MAPPING EVERY POSSIBLE OUTCOME FROM THE LAST 12 MONTHS TO SCORE IT.   I HOPE THIS EXPLAINS IT BETTER.
 | 2008-04-30 00:00:00 | 50.7
 | 2008-05-31 00:00:00 | 50.6
 | 2008-06-30 00:00:00 | 49.2
 | 2008-07-31 00:00:00 | 47.4
 | 2008-08-31 00:00:00 | 47.6
 | 2008-09-30 00:00:00 | 45
 | 2008-10-31 00:00:00 | 41.1
 | 2008-11-30 00:00:00 | 35.6
 | 2008-12-31 00:00:00 | 33.9
 | 2009-01-31 00:00:00 | 34.4
 | 2009-02-28 00:00:00 | 33.5
 | 2009-03-31 00:00:00 | 33.9 |  |  |  |  |  |  | A2 | Trough <50,growing for 1st time |  |  |  |  | A2 | Trough <50,growing for 1st time
 | 2009-04-30 00:00:00 | 36.8 |  |  |  |  |  | 2008-04-30 00:00:00 |  | 50.7 | -1.3 |  |  |  | 52.3
 | 2009-05-31 00:00:00 | 40.7 |  |  |  |  |  | 2008-05-31 00:00:00 | WILL STOP AND GO ACROSS TO NEXT CELL | 50.6 |  |  |  |  | 52
 | 2009-06-30 00:00:00 | 42.6 |  |  |  |  |  | 2008-06-30 00:00:00 | ↑ | 49.2 |  |  | ↓ |  | 50.7
 | 2009-07-31 00:00:00 | 46.3 |  |  |  |  |  | 2008-07-31 00:00:00 |  | 47.4 |  |  |  |  | 50.6
 | 2009-08-31 00:00:00 | 48.2 |  |  |  |  |  | 2008-08-31 00:00:00 |  | 47.6 |  | True |  |  | 49.2
 | 2009-09-30 00:00:00 | 49 |  |  |  |  |  |  |  |  |  | CHECKS DOWN AND FINDS 1 VALUE OVER 0 |  |  | 47.4
 | 2009-10-31 00:00:00 | 50.7 |  |  |  |  |  |  |  |  |  |  |  |  | 47.6 |  | ture
 | 2009-11-30 00:00:00 | 51.2 |  |  |  |  |  |  |  |  |  |  |  |  | 45
 | 2009-12-31 00:00:00 | 51.6 |  |  |  |  |  |  |  |  |  |  |  |  | 41.1
 | 2010-01-31 00:00:00 | 52.4 |  |  |  |  |  |  |  |  |  |  |  |  | 35.6
 | 2010-02-28 00:00:00 | 54.2 |  |  |  |  |  | A1 | Peak >50,slowing for 1st time |  |  | -8 to -10 |  |  | 33.9
 | 2010-03-31 00:00:00 | 56.6 |  |  |  |  |  |  |  |  |  |  |  |  | 34.4 |  | False
 | 2010-04-30 00:00:00 | 57.6 |  |  |  |  |  | 49 |  |  |  |  |  |  | 33.5
 | 2010-05-31 00:00:00 | 55.8 |  |  |  |  |  | 50.7 |  |  |  |  |  |  | 33.9 |  | False
 | 2010-06-30 00:00:00 | 55.6 |  |  |  |  |  | 51.2
 | 2010-07-31 00:00:00 | 56.7 |  |  |  |  |  | 51.6
 | 2010-08-31 00:00:00 | 55.1 |  |  |  |  |  | 52.4 |  |  |  |  | 49
 | 2010-09-30 00:00:00 | 53.7 |  |  |  |  |  | 54.2 |  |  |  |  | 50.7
 | 2010-10-31 00:00:00 | 54.6 |  |  |  |  |  | 56.6 |  |  |  |  | 51.2
 | 2010-11-30 00:00:00 | 55.3 |  |  |  |  |  | 57.6 |  |  |  |  | 51.6
 | 2010-12-31 00:00:00 | 57.1 |  |  |  |  |  | 55.8 |  | True |  |  | 52.4
 | 2011-01-31 00:00:00 | 57.3 |  |  |  |  |  |  |  |  |  |  | 54.2
 | 2011-02-28 00:00:00 | 59 |  |  |  |  |  |  |  |  |  |  | 56.6
 | 2011-03-31 00:00:00 | 57.5 |  |  |  |  |  |  |  |  |  |  | 57.6
 | 2011-04-30 00:00:00 | 58 |  |  |  |  |  |  |  |  |  |  | 55.8 |  | True
 | 2011-05-31 00:00:00 | 54.6 |  |  |  |  |  |  |  |  |  |  | 55.6 |  | False
 | 2011-06-30 00:00:00 | 52 |  |  |  |  |  |  |  |  |  |  | 56.7
 | 2011-07-31 00:00:00 | 50.4 |  |  |  |  |  |  |  |  |  |  | 55.1 |  | False
 | 2011-08-31 00:00:00 | 49 |  |  |  |  |  |  |  |  |  |  | 53.7 |  | False
 | 2011-09-30 00:00:00 | 48.5
 | 2011-10-31 00:00:00 | 47.1
 | 2011-11-30 00:00:00 | 46.4 |  |  |  |  | True
 | 2011-12-31 00:00:00 | 46.9
 | 2012-01-31 00:00:00 | 48.8
 | 2012-02-29 00:00:00 | 49
 | 2012-03-31 00:00:00 | 46
 | 2012-04-30 00:00:00 | 45.9
 | 2012-05-31 00:00:00 | 45.1
 | 2012-06-30 00:00:00 | 45.1
 | 2012-07-31 00:00:00 | 44
 | 2012-08-31 00:00:00 | 45.1
 | 2012-09-30 00:00:00 | 46.1
 | 2012-10-31 00:00:00 | 45.4
 | 2012-11-30 00:00:00 | 46.2
 | 2012-12-31 00:00:00 | 46.1
 | 2013-01-31 00:00:00 | 47.9
 | 2013-02-28 00:00:00 | 47.9
 | 2013-03-31 00:00:00 | 46.8
 | 2013-04-30 00:00:00 | 46.7
 | 2013-05-31 00:00:00 | 48.3
 | 2013-06-30 00:00:00 | 48.8
 | 2013-07-31 00:00:00 | 50.3
 | 2013-08-31 00:00:00 | 51.4 |  |  |  | True
 | 2013-09-30 00:00:00 | 51.1
 | 2013-10-31 00:00:00 | 51.3
 | 2013-11-30 00:00:00 | 51.6
 | 2013-12-31 00:00:00 | 52.7
 | 2014-01-31 00:00:00 | 54
 | 2014-02-28 00:00:00 | 53.2
 | 2014-03-31 00:00:00 | 53
 | 2014-04-30 00:00:00 | 53.4
 | 2014-05-31 00:00:00 | 52.2
 | 2014-06-30 00:00:00 | 51.8
 | 2014-07-31 00:00:00 | 51.8
 | 2014-08-31 00:00:00 | 50.7
 | 2014-09-30 00:00:00 | 50.3
 | 2014-10-31 00:00:00 | 50.6
 | 2014-11-30 00:00:00 | 50.1
 | 2014-12-31 00:00:00 | 50.6
 | 2015-01-31 00:00:00 | 51
 | 2015-02-28 00:00:00 | 51
 | 2015-03-31 00:00:00 | 52.2
 | 2015-04-30 00:00:00 | 52
 | 2015-05-31 00:00:00 | 52.2
 | 2015-06-30 00:00:00 | 52.5
 | 2015-07-31 00:00:00 | 52.4
 | 2015-08-31 00:00:00 | 52.3
 | 2015-09-30 00:00:00 | 52
 | 2015-10-31 00:00:00 | 52.3
 | 2015-11-30 00:00:00 | 52.8
 | 2015-12-31 00:00:00 | 53.2
 | 2016-01-31 00:00:00 | 52.3
 | 2016-02-29 00:00:00 | 51.2
 | 2016-03-31 00:00:00 | 51.6
 | 2016-04-30 00:00:00 | 51.7
 | 2016-05-31 00:00:00 | 51.5
 | 2016-06-30 00:00:00 | 52.8
 | 2016-07-31 00:00:00 | 52
 | 2016-08-31 00:00:00 | 51.7
 | 2016-09-30 00:00:00 | 52.6
 | 2016-10-31 00:00:00 | 53.5
 | 2016-11-30 00:00:00 | 53.7
 | 2016-12-31 00:00:00 | 54.9
 | 2017-01-31 00:00:00 | 55.2
 | 2017-02-28 00:00:00 | 55.4
 | 2017-03-31 00:00:00 | 56.2
 | 2017-04-30 00:00:00 | 56.7
 | 2017-05-31 00:00:00 | 57
 | 2017-06-30 00:00:00 | 57.4
 | 2017-07-31 00:00:00 | 56.6
 | 2017-08-31 00:00:00 | 57.4
 | 2017-09-30 00:00:00 | 58.1
 | 2017-10-31 00:00:00 | 58.5
 | 2017-11-30 00:00:00 | 60.1
 | 2017-12-31 00:00:00 | 60.6
 | 2018-01-31 00:00:00 | 59.6
 | 2018-02-28 00:00:00 | 58.6
 | 2018-03-31 00:00:00 | 56.6
 | 2018-04-30 00:00:00 | 56.2
 | 2018-05-31 00:00:00 | 55.5
 | 2018-06-30 00:00:00 | 54.9
 | 2018-07-31 00:00:00 | 55.1
 | 2018-08-31 00:00:00 | 54.6
 | 2018-09-30 00:00:00 | 53.2
 | 2018-10-31 00:00:00 | 52
 | 2018-11-30 00:00:00 | 51.8
 | 2018-12-31 00:00:00 | 51.4
 | 2019-01-31 00:00:00 | 50.5
 | 2019-02-28 00:00:00 | 49.3
 | 2019-03-31 00:00:00 | 47.5 |  |  |  |  | True
 | 2019-04-30 00:00:00 | 47.8
 | 2019-05-31 00:00:00 | 47.7
 | 2019-06-30 00:00:00 | 47.8
 | 2019-07-31 00:00:00 | 46.4
 | 2019-08-31 00:00:00 | 47
 | 2019-09-30 00:00:00 | 45.6
 | 2019-10-31 00:00:00 | 45.7
 | 2019-11-30 00:00:00 | 46.6
 | 2019-12-31 00:00:00 | 45.9
 | 2020-01-31 00:00:00 | 47.8
 | 2020-02-28 00:00:00 | 49.1
 | 2020-02-29 00:00:00 | 49.2
 | 2020-03-31 00:00:00 | 44.8
 | 2020-04-30 00:00:00 | 33.6
 | 2020-05-01 00:00:00 | 39.4
 | 2020-06-01 00:00:00 | 47.4
 | 2020-07-01 00:00:00 | 51.5
 | 2020-08-01 00:00:00 | 51.7
 | 2020-09-01 00:00:00 | 53.7
 | 2020-10-01 00:00:00 | 54.4