SOLVED

Index Match - retrieve a value between 2 dates across different rows

Brass Contributor

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 NameField / EventOld ValueNew ValueEdit Date
APP-102760Probability of Arrival 205/12/2022
APP-102760Probability of Arrival2311/01/2023
APP-102760Probability of Arrival3421/02/2023
APP-102760Probability of Arrival4522/05/2023
14 Replies
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/2023

@JennySommet 

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.

@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!

@JennySommet 

You are welcome!!

Can you please change $F1 in the formula to $F$2 and check if the expected results are returned.

index large.png

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 

Hi @JennySommet ,

can you change $F2 to $F$2 and check if the formula returns the intended results?

index and large.png

@OliverScheurich 

 

Thank you! that helped a lot! So close now. I think the formula needs to be adjusted for ones where there is only ONE Edit Date, for example the one below should return the value '4' for date 20/05/2023 but the current formula brings back #NUM!

 

Application: Application NameField / EventOld ValueNew ValueEdit Date
APP-1450Probability of Arrival4517/07/2023

@OliverScheurich 

 

I just thought, does the Application: Application Name and Edit Date columns need to be sorted for the formula to work? 

@JennySommet 

=IFERROR(INDEX('Application History Report'!$F$2:$F$8589,LARGE(IF(('Application History Report'!$A$2:$A$8589=B2)*('Application History Report'!$G$2:$G$8589<=$Z$1),ROW('Application History Report'!$G$2:$G$8589)-1),1)),INDEX('Application History Report'!$E$2:$E$8589,MATCH(B2,'Application History Report'!$A$2:$A$8589,0)))

 

You are welcome. It should work if you enter this formula in cell X2 of sheet "Rel Week CI+ 20th May 2023.2". The formula must be entered as an arrayformula. You are right the data in sheet "Application History Report" must be sorted by Application: Application name and then by Edit Date. The Edit Date must be sorted oldest to newest.

 

Please remove your file from your replies because it seems to contain names of real people. I've just noticed this when i've edited the file. 

 

The formula returns some #NA errors for applications which don't appear at all in sheet "Application History Report" for example APP-23796.

 

If the Edit Date in cell $Z$1 is before the earliest date in sheet "Application History Report" then the formula returns the Old Value. For APP-1450 the result is now the Old Value 4. 

@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!

best response confirmed by JennySommet (Brass Contributor)
Solution

@JennySommet 

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

formula.png

@OliverScheurich 

 

Uh silly mistakes so sorry! 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?!

@JennySommet 

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.

@OliverScheurich 

 

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!

1 best response

Accepted Solutions
best response confirmed by JennySommet (Brass Contributor)
Solution

@JennySommet 

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

formula.png

View solution in original post