Excel worksheet multi-criteria function like Index-Match array to fetch last value of an item looking up by item name and date

Question

I'm providing an example table with a list of goods and their current prices. My Index-Match array method allows you to check up a product's pricing for a specific month. But I'm looking for a function that returns the most recent value entered for a product. So, the function should look up the product name, then use the date column to find the most recent date on which the product has a record, then retrieve the price for that row. You can't merely use the most recent date because not all goods are updated every month. The expiration dates for different products will vary. (For instance, only product #3333 in the current calculator has an entry for 5/1/22, therefore the other two display #N/A. That's okay.)

I'm using this formula in current calculator, cell F5 (as in image):

=INDEX($C$4:$C$12, MATCH(1,(E5=$B$4:$B$12)*($G$4=$A$4:$A$12),0))

Answer 1

Use:

=SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12))

It will return the value at the last date where the product number matches.