Max on an INDEX-MATCH

toongal12

Board Regular
Joined
Dec 1, 2016
Messages
150
My data comes from a pivot table. I am trying to list each release of an item when an item is updated, so a certain item may be listed multiple times. My formula will grab the most current upgrade of the year.


A2 = first item on list of multiple items
B1 = End of December to mark a new year


=MAX(IF((ItemData[ItemName]=A2)*(ItemData[CompletedData]<=$B$1),VALUE(ItemData[Version])),0)


Version is set to be 1 for true it is an upgrade, and 0 there is no upgrade


The above formula works, but once one Version goes "1" for true, the remaining years will show "1" since that is the max value for that item. Even if the next year has a "0" in the Version column, it still returns "1".


I am not sure how to get the max upgrade of the year, while still able to grab the upgrade true/false from the Version column of that row.


I tried INDEX-MATCH but it just returns the item


=INDEX(ItemData, MATCH(1, (ItemData[ItemName]=A2)*(ItemData[CompletedDate]<=$B$1), 0),ItemData[Version])
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

toongal12

Board Regular
Joined
Dec 1, 2016
Messages
150
Could you provide an example of your data?

ItemNameCompletedDateVersion
Item112/1/160
Item311/4/161
Item29/8/120
Item14/9/141
Item17/2/130
Item311/5/171

<tbody>
</tbody>


So it will find Item1, which there are three instances on the list. Next, it will find the most current version compared to a specific date (let's say 12/14/16). Then it will produce either the 0 or 1 to say if it's been updated.

With my formula now, it outputs the max of each, so ifor today's date, it will return a "1" because there was once a max of "1" on 4/9/14, when it should really return "0" from the row of the most current update.

I thought INDEX-MATCH would work but I do not know enough about it :(
 
Upvote 0

godsaaint

Active Member
Joined
Sep 16, 2016
Messages
285
Try something like this:

Formula in D11(array formula: CTRL-SHIFT-ENTER):

=INDEX($C$2:$C$7,MATCH(LARGE(IF($A$2:$A$7=D10,$B$2:$B$7),1),$B$2:$B$7,0))

ItemNameCompletedDateVersion
Item101/12/20160
Item304/11/20161
Item208/09/20120
Item109/04/20141
Item102/07/20130
Item305/11/20171
Lookup ValueItem1
Result0

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>
 
Upvote 0

toongal12

Board Regular
Joined
Dec 1, 2016
Messages
150
Try something like this:

Formula in D11(array formula: CTRL-SHIFT-ENTER):

=INDEX($C$2:$C$7,MATCH(LARGE(IF($A$2:$A$7=D10,$B$2:$B$7),1),$B$2:$B$7,0))

ItemNameCompletedDateVersion
Item101/12/20160
Item304/11/20161
Item208/09/20120
Item109/04/20141
Item102/07/20130
Item305/11/20171
Lookup ValueItem1
Result0

<tbody>
</tbody>

It looks like the formula is just telling me the max overall? I need the most current version upgrade at the time of the date listed in a cell (example, if at 3/5/13, it will return 0, if at 9/7/14, it will return 1). I tried to modify it but it does not work. Data comes from pivot table.

=INDEX(ItemData[Version], MATCH(LARGE(IF(ItemData[ItemName]=A2)*(ItemData[CompletedDate]<=$B$1), 1),(ItemData[CompletedDate],0))
 
Last edited:
Upvote 0

godsaaint

Active Member
Joined
Sep 16, 2016
Messages
285
It looks like the formula is just telling me the max overall? I need the most current version upgrade at the time of the date listed in a cell (example, if at 3/5/13, it will return 0, if at 9/7/14, it will return 1). I tried to modify it but it does not work. Data comes from pivot table.

=INDEX(ItemData[Version], MATCH(LARGE(IF(ItemData[ItemName]=A2)*(ItemData[CompletedDate]<=$B$1), 1),(ItemData[CompletedDate],0))

The formula I provided gives you the most recent version. If I follow correctly, you want to be able to specify a date in the formula so you can get the version depending of the date you look up?
 
Last edited:
Upvote 0

toongal12

Board Regular
Joined
Dec 1, 2016
Messages
150
The formula I provided gives you the most recent version. If I follow correctly, you want to be able to specify a date in the formula so you can get the version depending of the date you look up?


Yes. I'll change it so D1 holds a date. At that date, what is the current version of an item.

=INDEX(ItemData[Version], MATCH(LARGE(IF(ItemData[ItemName]=A2)*(ItemData[CompletedDate]<=$D$1), 1),(ItemData[CompletedDate],0))


Example:
If D1 = 3/5/13, it will return 0
If D1 = 9/7/14, it will return 1

It is the most current compared to a specific date
 
Upvote 0

godsaaint

Active Member
Joined
Sep 16, 2016
Messages
285
Ok, try this(CTRL-SHIFT-ENTER), D10 being the item you are looking for, and D9 the date:

=INDEX($C$2:$C$7,SMALL(IF(IF($A$2:$A$7=D10,$B$2:$B$7)<=D9,ROW($B$2:$B$7)-1),1))
 
Upvote 0

Forum statistics

Threads
1,191,228
Messages
5,985,385
Members
439,961
Latest member
drose1105

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top