Forum Discussion

JennyHoA20181's avatar
JennyHoA20181
Brass Contributor
May 20, 2024

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 th...
  • OliverScheurich's avatar
    OliverScheurich
    May 21, 2024

    JennyHoA20181 

    =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.

Resources