I have a table in which I record COVID mortalities which I am using for a research project. I have succeeded in finding the peak value for each wave wave of the pandemic, using the start and end date of each wave.
1. My E-page contains the date under column A, the total number of mortalities under column B and the difference between the current date and the day before in column C.
2. the cell AQ3 contains the date on which the wave started and AW3 contains the date when the wave ended.
3. The following formula extracts the highest value between the two dates (in this case the value is 44 and appears in the cell =E!C682)
=MAX(INDIRECT(CELL("address",INDEX(E!$A:$C,MATCH(AQ3,E!$A:$A,0),3))):INDIRECT(CELL("address",INDEX(E!$A:$C,MATCH(AW3,E!$A:$A,0),3))))
4. I now want the date which corresponds to that value, i.e., I want the value in Cell =E!A682
5. I tried the formula =INDEX(E!$A:$A,MATCH(AZ3,E!$C:$C,0)), but because the number 44 occurs more than once in the table, the date is incorrect.
I am trying to find a way to change the above MATCH formula so that it only finds the match between the two dates in AQ3 and AW3.
Alternatively, I want to find the date in the cell in column A which corresponds to the cell in column C where I extracted the value of "44". The correct date should be 02-Dec-2021 and not 23-Nov-2021 as this falls before the start of the fourth wave (29 November)
Below is how the raw data appears in my page E
A B C
671 21-Nov-2021 89574 2
672 22-Nov-2021 89584 10
673 23-Nov-2021 89628 44
674 24-Nov-2021 89657 29
675 25-Nov-2021 89771 114
676 26-Nov-2021 89783 12
677 27-Nov-2021 89791 8
678 28-Nov-2021 89797 6
679 29-Nov-2021 89822 25
680 30-Nov-2021 89843 21
681 01-Dec-2021 89871 28
682 02-Dec-2021 89915 44
683 03-Dec-2021 89944 29
684 04-Dec-2021 89965 21
Any help would be highly appreciated.
Thanks
Arnau
Bookmarks