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.
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
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.
How to use name to find max or latest date in another sheet?
Use the sheet name before the range. let's say you want to find in sheet 1 then use this formula.
{=INDEX(Sheet1!$D$2:$D$10,MATCH(1,INDEX((Sheet1!$C$2:$C$10=$F$3)*(Sheet1!$B$2:$B$10=MAX(IF(Sheet1!$C$2:$C$10=F3,Sheet1!$B$2:$B$10))),0),0))}
Thank you very MUCH!!!!!!!!! Help me a lot!!!
What simple explanation!!!!!!