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.
JennyHoA20181
May 21, 2024Brass Contributor
OliverScheurich This is brilliant! I think I am doing something wrong, because I entered as an array this time, and sorted the Application Name and Edit Date in the Application history tab - although I only had to sort by Edit date before, it didn;t seem to matter if the Application Name was not sorted?
Anyway, now the formula does not work at all...
Oops I have deleted the names this time!
OliverScheurich
May 21, 2024Gold Contributor
=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.
- JennyHoA20181May 21, 2024Brass Contributor
It all works beautifully! Weirdly I didn't need to use Array?
Thank you x 1 million! You've save me so much time and stress and helped our team to create a solid validation process of data moving forwards.
You're amazing!
- OliverScheurichMay 21, 2024Gold Contributor
Unfortunately i didn't ask you to remove the array in the first step. Can you select column S from cell S2 down to cell S1149. In this range there is currently an array which returns 4 in all these cells. This is the result for the value in cell B2. After selecting range S2:S1149 delete the array from this range. Then enter the formula in cell S2 and confirm it with ctrl+shift+enter. Then fill the formula down.
- JennyHoA20181May 21, 2024Brass Contributor
I have now got the formuli right, but I'm getting far too many 'false' values in column Y, whereas before I only had approx 100. I'm not sure what has happened?!