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.

 

img1

 

  • 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.

 

img2

 

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:

 

img3

 

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

 

image 4

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

Users are saying about us...

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube