使用 VLOOKUP 记录最新日期

Question

我正在创建一个电子表格来监控我们上次联系客户的时间。在“概览”选项卡中，我列出了所有客户姓名和地址，例如：

A	乙	C	德	埃	F
姓名	地址	电话	下次联络日期	需要采取的行动	特别说明
A.安德森	123 假街	00000	2021 年 1 月 7 日	是的	不会说英语

在“日志”选项卡中，我有一个 VLOOKUP，有人会在其中输入姓名，然后它会拖过他们的联系方式，例如=IFERROR(VLOOKUP(A2,Overview!$A$1:$F$442,2,FALSE),"Ensure Name is Exactly as shown on 'Overview' tab")

“日志”选项卡的布局如下：

A	乙	C	德	埃	F	G	H
姓名	地址	联系日期	结果	需要采取的行动	行动已完成	下次联络日期	特别说明
A.安德森	123 假街	2021 年 1 月 2 日	完全的	不	2021 年 1 月 5 日	-	不会说英语
A.安德森	123 假街	2021 年 1 月 5 日	完全的	是的	2021 年 5 月 5 日	2021 年 1 月 7 日	不会说英语

我举了一个例子，其中有两条记录是同一个客户的。这是因为我想保留联系人记录，因此将为每个联系人添加一个新行。但在“概览”选项卡中，我希望“下次联系日期”、“所需行动”， 和特别说明更新时自动从“日志”选项卡中显示。我使用 VLOOKUP 来恢复数据，但现在两个记录中有重复项，Excel 只恢复一个 - 最旧的记录。而我希望只恢复最新的信息。每次添加新行时，它都应该更新“概览”选项卡中的相关记录。

我使用的“概述”D 列的公式如下：=IFERROR(VLOOKUP(A2,Log!$A$1:$O$8,7,FALSE),"")

有没有办法将最近日期的检查合并到 VLOOKUP 函数中？我在网上搜索过，但找到的每个解决方案都涉及返回所有记录，而不仅仅是最近的记录。

Answer 1

如果你有较新版本的 Excel，请使用=XLOOKUP()

你的公式就变成：=XLOOKUP(A2,Log!$A$1:$A$8,Log!$G$1:$G$8,"",0,-1)

xlookup 所做的一件事是将 iferror 和 vlookup 合并为 1 个函数。它做的另一件事是它可以向后搜索。因此，只要您的日志按时间顺序排列，您就可以使用 -1 作为最后一项，它就会向后搜索（当然，您可以继续使用公式，只需对表格数组进行排序，将最新的放在最上面...）

Answer 2

如果您不介意添加辅助列，那么这里有一种方法。在您的 LOG 表中，添加另一列。第 I 列的公式 (Max_Contact_Flag)...

=IF(MAXIFS(C:C,A:A,[@Name])=[@[Date Contacted]],"Yes","No")

此标志查找每个人的最近联系日期，如果最大日期与该人当前行的日期匹配，则返回 YES 值。

如果您不使用表格，则将 @Variables 替换为列范围。但我强烈建议使用表格，因为添加新行时辅助列会自动重新计算，因此无需继续拖动公式。

然后，您可以过滤表中的 YES 值，或者也可以创建数据透视表。在透视表中，将过滤器设置为 Yes。此透视表将代表您的概览。

Answer 3

为了便于理解，请将您的日志转换为带标题的 Excel 表 (CTRL t)。我将其命名为日志：

然后在概述中，从单元格 C2 开始，输入此公式并将其向下拖动：

= INDEX(XLOOKUP(A2,INDEX(SORTBY(tLog,tLog[Date Contacted],-1),,1),INDEX(SORTBY( tLog,tLog[Date Contacted],-1 ),,)),1,{2,7,5,8})

在哪里A2是您正在查找的人的姓名。{2,7,5,8}是您想要从 tLog 中检索的列的列表。（注意：如果您将其留空，它将抓取所有列。）

这很难阅读，因此您可以通过定义一个来简化它并加快计算时间命名范围在里面“公式”选项卡。

你可以将排序表命名为_稳定的并将其设置为以下公式：

=SORTBY( tLog, tLog[Date Contacted], -1 )

这将允许您在 C2 中输入更易读且更快速的公式：

= INDEX(XLOOKUP(A2,INDEX(_sTable,,1),INDEX(_sTable,,)),1,{2,7,5,8})

结果将如下所示：

它的工作方式是，它会在内存中创建一个新版本的日志，并按以下方式排序：下次联络日期然后对该表而不是日志表执行 XLOOKUP 搜索。