Index & Match find value with new Date

Index & Match to Find Value by Latest Date in Microsoft Excel

If you are looking for a formula to find the lookup value & latest value by date, then this is article is really helpful for you. In this article, we will learn how to find the matching value & then formula will check the output after checking the latest date.

Question): I need a formula to look up each specific product then find the price of the product on the latest date.

Following is the snapshot of data i.e. column A has Invoice No., column B has Date, column C has Product & column D has Prices.

• The formula we are looking for should first of all check the Product from column C & then return the Price for the latest date.

• We will use a combination of INDEX, MATCH & MAX functions to return the output.
• In cell G3, the formula is
• {=INDEX($D$2:$D$10,MATCH(1,INDEX(($C$2:$C$10=$F$3)*($B$2:$B$10=MAX(IF($C$2:$C$10=F3,$B$2:$B$10))),0),0))}

Note: This is an array formula. Hence, we need to press CTRL + SHIFT + END keys together to get the correct result.

The above formula has used Max function to determine the latest date from all the given dates for the matching Product from column C.

To test the above formula if we change the date in cell B7, then we will get the desired result. Refer below snapshot:

In this way, we can deliver the price of the product with the latest date.

Download - Index & Match To Find Value By Latest Date - xlsx