Good day Excel experts. I have attached an example spreadsheet. It is a spreadsseet I intend to update daily with the date being column A, and a given numerical value in column B. What I want to do here is have the spreadhseet display in cell E5, the LAST maximum value in column B, and the corresponding date of that value in cell G5. Obviously using "=max(b:b)" in cell E5 will return the value 50 as this is the maximum numerical value in column B, but I want the spreadsheet to look up and display in cell E5, the last maximum value in column B,which in my example spreadsheet can be found in cell B15, or 26. Then I want to know what formula to use in cell G5 to return the date when this value occured, or 2024-01-11, as in cell A15.

Then each day as the list is updated, I want the values in cell E5 and G5 to recalculate and display the last occurring maximum value that is in column B, based on the date, so it should look back and find the previous date with the maximum value of column B and display that If the current cell on a given date is equal to or already the maximum value in column B, then display that value and date in E5 and G5. Please let me know if any questions, and thanks for your help and suggestions

Attached Files

EXCEL FORUM EXAMPLE.xlsx (9.7 KB, 6 views)