EXCEL TABLE:根据来自多个条件的列中的最新日期查找值 [英] EXCEL TABLE: Find Value based on Latest Date in Column from Multiple Criteria

问题描述

我正在尝试计算如何根据另一个条件(基于多个条件选择)从Excel(Office 365)表中的列中获取值.我已经写了公式以获取最新日期，现在我需要从另一列中获取相应的值.我希望始终在F列中获得每个客户的最新评论，因此对于CAMCO，它将是最新记录.

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.

以下示例:

我在E列中的公式是= {MAX(IF([Customer] = B2，[Date]))}

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

推荐答案

要查找最新的Note，请使用

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

这篇关于EXCEL TABLE:根据来自多个条件的列中的最新日期查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！