问题描述
我正在尝试计算如何根据另一个条件(基于多个条件选择)从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:根据来自多个条件的列中的最新日期查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！