Match last value in column with blanks on multiple criteria

Question

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.

Answer 1

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.

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)

Answer 2

Use LOOKUP:

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