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.
=LOOKUP(2,1/((DataRange1=Criteria1)*(DataRange2=Criteria2)),ReturnRange)
. Though I'm not sure what exactly your criteria is. – JvdVMAXIFS
?), 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... – ashleedawg1E+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