Forum Discussion
JennyHoA20181
May 20, 2024Brass Contributor
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...
- May 21, 2024
=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.
OliverScheurich
May 20, 2024Gold Contributor
The 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.
JennyHoA20181
May 20, 2024Brass Contributor
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!
- OliverScheurichMay 20, 2024Gold Contributor
You are welcome!!
Can you please change $F1 in the formula to $F$2 and check if the expected results are returned.
- JennyHoA20181May 20, 2024Brass Contributor
Oops, silly mistake sorry!
I changed it however and it's still not bringing back the desired results. Row 5, for example should bring back the value '4' because that's what it was 20/05/2023. I can't think what might be causing it... OliverScheurich
- OliverScheurichMay 20, 2024Gold Contributor