Match last value in column with blanks on multiple criteria

0
votes

I have a table (T_Data) that - for the sake of this post - is structured similar to the following:

StoreID DateStamp Inventory Sold
0000001 2020-12-26 10000 500
0000002 2020-12-26 100
0000001 2020-12-27 400
0000002 2020-12-27 5000 200
0000001 2020-12-28 400
0000002 2020-12-28 200
0000001 2020-12-29 400
0000002 2020-12-29 200
0000001 2020-12-30 400
0000002 2020-12-30 200

Inventory is a physical count, which happens once a month per store, but the date may vary. I need to return the last physical count per StoreID. I can leverage the following for ALL stores, but I can't figure out how to translate that to use multiple criteria:

=MATCH(9.99999999999999E+307,T_Data[Inventory])

(Returns 5000)

I'm using Excel 2016, so Sort is out of the question. I'm also avoiding VBA because it'll cause issues with my staff figuring out how to get around warnings.

2
excelmatch
why are you trying to match using scientific notation? – ashleedawg
I'd use a lookup Boolean structure. E.g: =LOOKUP(2,1/((DataRange1=Criteria1)*(DataRange2=Criteria2)),ReturnRange). Though I'm not sure what exactly your criteria is. – JvdV
@ashleedawg by looking up a ridiculously large number and using the default lookup type it will return the last cell with a number. – Scott Craner
ummm ok. I can think of better ways to find the last cell (ie,MAXIFS?), but also wouldn't looking up just a "large" number rather than "ridiculously" large would also do the trick, no? Unless some inventory items actually have "10 centillion" items in stock... – ashleedawg
@ashleedawg my default for this type is 1E+99 which as you stated is usually sufficient, and much easier to type. And MATCH is much quicker than LOOKUP if a single lookup. One off formula will not see a difference but a column full would. – Scott Craner

2 Answers

1
votes

Use LOOKUP:

=LOOKUP(2,1/((T_Data[Inventory]<>"")*(T_Data[StoreID]=F4)),T_Data[Inventory])

enter image description here

0
votes

Alternatively, you could use MAXIFS to find the date of the last non-blank inventory and then use XLOOKUP to find the Inventory on that date. I think XLOOKUP is in Excel2016.

enter image description here

Formula for G2:

=MAXIFS($B$2:$B$11,$C$2:$C$11,"<>",$A$2:$A$11,F2)

Formula for H2:

=XLOOKUP(F2&G2,$A$2:$A$11&$B$2:$B$11,$C$2:$C$11)