Forum Discussion
Index Match - retrieve a value between 2 dates across different rows
Hi all,
I am hoping someone can help me! It's a very tricky one! In the table below, I need a formula that tells me that for APP-102760 the value was 3 on the 30th January. The edit date shows the date the value changed from 'Old Value' to 'New Value'. As the 30th January is between Edit Dates 11/01/2023 and 21/02/2023, I can see that the value was 3.
| Application: Application Name | Field / Event | Old Value | New Value | Edit Date |
| APP-102760 | Probability of Arrival | 2 | 05/12/2022 | |
| APP-102760 | Probability of Arrival | 2 | 3 | 11/01/2023 |
| APP-102760 | Probability of Arrival | 3 | 4 | 21/02/2023 |
| APP-102760 | Probability of Arrival | 4 | 5 | 22/05/2023 |
=IFERROR(INDEX('Application History 2023.2'!$D$2:$D$8589,LARGE(IF(('Application History 2023.2'!$A$2:$A$8589=B2)*('Application History 2023.2'!$E$2:$E$8589<=$V$1),ROW('Application History 2023.2'!$G$2:$G$8589)-1),1)),INDEX('Application History 2023.2'!$C$2:$C$8589,MATCH(B2,'Application History 2023.2'!$A$2:$A$8589,0)))
You are right! The database in the Application History 2023.2 sheet only needs to be sorted by the Edit Date column from oldest to newest.
The name of the sheet is now Application History 2023.2 therefore the formula must refer to this sheet.
After some columns were deleted the formula must be adjusted for example:
('Application History 2023.2'!$E$2:$E$8589<=$V$1)
because the date is now in $V$1 before it was in cell $Z$1. Now the dates are in column E and before they were in column G in the Application History 2023.2 sheet.
The formula must be entered as an arrayformula not as an array. Try to enter the formula in cell S2 of the "Rel Week CI+ 20th May 2023.2" sheet and confirm it with ctrl+shift+enter. Then fill the formula down.
- Just to add a possible complexity, in the example below, If I want to show that for APP-0779 the value was 0 on 12th May, the formuli would need to consider the last / max value for 12th May, since the value changed twice that day.
Application: Application Name Field / Event Old Value New Value Edit Date
APP-0779 Probability of Arrival 2 30/11/2022
APP-0779 Probability of Arrival 2 1 18/01/2023
APP-0779 Probability of Arrival 1 2 18/01/2023
APP-0779 Probability of Arrival 2 1 25/01/2023
APP-0779 Probability of Arrival 1 0 01/02/2023
APP-0779 Probability of Arrival 0 4 12/05/2023
APP-0779 Probability of Arrival 4 0 12/05/2023
APP-0779 Probability of Arrival 0 3 15/05/2023
APP-0779 Probability of Arrival 3 4 17/05/2023
APP-0779 Probability of Arrival 4 5 18/07/2023The formula in the sample file returns the expected result if i correctly understand what you want to do. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
OliverScheurich Thank you!!!!
Can you advise, I have inserted the formuli into my main report, but it's not working as well. Please see row 5 in tab 'Rel Week CI+ 20th May 2023.2'. I have inserted your formula into column X and the value should be 4 not 2, when I lookup APP-3499 in the 'Application History Report' tab against date 20th May. I am also getting lots of values that say #NUM!.
Thank you once again!
