CODEWITHSUNDEEP
arraysexcelexcel-formula
Andy L
Andy L

Reputation: 29

How to pull the most recent change on an item in Excel using arrays

I'm trying to pull the most recent change to an item from a list of values and dates.

I've pasted a URL below for the image of the spreadsheet I'm currently working on. This is currently in the top left of the spreadsheet, starting in cell A1.

I am trying to display in column C the "New Value" where columns A and E match, and where it is the most recent date in column F before the date listed in column B.

For C2, it would be 1.99
For C3, it would be 2.99
For C4, it would be 3.99

I'm trying to use INDEX, MATCH, and MAX to create an array and return the row number, This is what my formula currently looks like:

=INDEX($H$2:$H$4,MAX(MATCH(1,(($E$2:$E$4=A2)*($F$2:$F$4<B2)),0)),4)

However I can't quite get it to display the correct values. When I evaluate the formula, it looks like the array is evaluating correctly, However I am having a difficult time because where it says {1,1,0} for C3, it will only result in the first match.

How can I change this array formula to only look at the last match row?

Spreadsheet Here

Upvotes: 1

Views: 276

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use MAXIFS inside a SUMIFS:

=SUMIFS(H:H,E:E,A2,F:F,MAXIFS(F:F,F:F,"<="&B2,E:E,A2))

enter image description here

IF one does not have MAXIFS, we can replace with AGGREGATE:

=SUMIFS(H:H,E:E,A2,F:F,AGGREGATE(14,7,$F$2:$F$4/(($F$2:$F$4<=B2)*(E2:E44=A2)),1))

or if one has the Dynamic Array Formula:

=INDEX(SORT(FILTER(E:H,(E:E=A2)*(F:F<=B2)),2,-1),1,4)

enter image description here

Upvotes: 1

Related Questions