I am looking for help with an Excel formula. What I need is to return the chemical and quantity with the latest date in another table showing the chemical and quantity.
1 Answers
One possibility is to put the whole output table into an array that spills. With Office 365 you can do this LET formula:
=LET( a, A5:C19,
f, FILTER( a, INDEX( a, , 1) <> "" ),
c, INDEX( f, , 1), q, INDEX( f, , 2), d, INDEX( f, , 3),
sf, SORTBY(f,c,1,d,1),
CHOOSE( {1,2}, UNIQUE( c ), XLOOKUP( UNIQUE( c ), c, q,, 0,-1 ) ) )
where you input your entire range of Chemical, Quantity and Actual Date values into a variable called a. The rest of the formula creates a sorted lookup table and then searches your left side filter formula to find the last match.
Related