EXCEL TABLE: Find Value based on Latest Date in Column from Multiple Criteria

0
votes

I am trying to work out how to get a value from a column in an Excel (Office 365) Table based on the latest date from another column which is selected based on multiple criteria. I have written the formula to get the latest date, now i need to get a corresponding value from another column. I want to always get the last comment per customer in Column F so for CAMCO it would be Last Note.

An example below:

My formula in Column E is ={MAX(IF([Customer]=B2,[Date]))}

enter image description here

1
excelexcel-formulaexcel-tables
which version of Excel? The answer will vary depending on version – chris neilsen
Sorry should have mentioned version, i am using office 365 – ccocker

1 Answers

0
votes

To find the latest Note, use

=INDEX([Note], MATCH([@Customer] & "_" & MAXIFS([Date], [Customer], [@Customer]), [Customer] & "_" & MAXIFS([Date], [Customer], [@Customer]), 0))

Or, if column Latest Date per Customer is a perminant addition

=INDEX([Note], MATCH([@Customer] & "_" & @[Latest Date per Customer], [Customer] & "_" & [Latest Date per Customer], 0))

FYI, your Latest Date lookup is better as

=MAXIFS([Date], [Customer], [@Customer])

Both are standard (not array) formula