时间戳

首页 > 解决方案 > 如何执行优先考虑某些值的 VLOOKUP（或类似）？

excel-formula - 如何执行优先考虑某些值的 VLOOKUP（或类似）？

问题描述

我有一个电子邮件和 UTM 源列表，我需要对其执行查找以返回源，但如果它们存在，我需要将公式默认为某些值。请参见下面的示例表

|email       |source  |
|------------|--------|
|123@xyz.com |email   |
|123@xyz.com |direct  |
|456@abc.com |organic |
|456@abc.com |direct  |

在这种情况下，我希望选择直接源而不是任何其他源，但是VLOOKUP将选择与查找值对应的第一个值。除了基本的VLOOKUP. 我已经尝试过INDEX(MATCH(，但它似乎只是做同样的事情。

我试图找到类似示例的解决方案，但在每种情况下我都遇到过它涉及可以使用MIN/MAX. 这是我可以在 Power Query 中更轻松地完成的事情吗？我可以在直接前面放一个 1，然后对源列 AZ 进行排序吗？这似乎可行，但我不确定在使用它之前它有多可靠。

标签： excel-formula

解决方案

如果我理解得很好，您希望通过自定义顺序返回源，其中 Direct 优先于所有其他顺序，问题是 VLOOKUP、INDEX(MATCH 和 XLOOKUP 都按字母顺序提供第一个（或最后一个）匹配项，而不考虑你的自定义优先级，所以他们不会给你你想要的结果。

我看到下面说明的两种方法。为了更清楚地说明这一点，我将您的表格转换为我命名为tContacts的 Excel 表格 (CRTL t)，并在其中添加了第三个电子邮件地址和“社交”来源，以说明方法 2 的优势。

联系人

快速回答

您可以直接跳到下面的 NOTES 并使用命名范围实现方法 1，以测试这是否真的有效。其余的只是解释它是如何工作的，以及如果我误解了或者你想进一步修改它，你可以选择什么。

方法 1 XISs - 保持简单，没有帮助

在这里，您将输入一个查找值（我将我的放在 D9 中），为了便于理解，我将其命名为lookupVal。我使用 LET 使这更清晰并加快计算速度，但这不是必需的 - 如果您愿意，您可以将所有内容折叠到下面的最终 XLOOKUP 中。

=LET( lookupVal, D9,
       sTable, SORTBY( tContacts, -(tContacts[source] = "direct") ),
       XLOOKUP( lookupVal, INDEX( sTable, , 1 ),INDEX( sTable, , 2 ) ) )

这将创建一个排序表，我称之为sTable §，它使用 SORTBY 根据您的规则对您的联系人表 (tContacts) 进行排序，即“直接”使用此布尔语句优先于所有其他来源-(tContacts[source] = "direct")。所以sTable 是按您的规则排序的 tContacts，如下所示：

sTable 简单规则

它对 sTable 的第 1 列执行 lookupVal 的 XLOOKUP 并提供 sTable 的第 2 列作为结果。

方法 2 XISt - 带有辅助表的扩展优先级

但是，如果您的优先事项更复杂怎么办？如果你想扩展你的优先级，你可以创建一个表来对它们进行排序，我称之为tSrcPri。

tSourcePrio

这就是为什么我在表格中添加了第三封电子邮件并添加了一个虚构的社交，以便我们可以看到直接 > 社交 > 有机 > 电子邮件的结构化优先级的价值。这是公式：

=LET( lookupVal, D9,
       sTable, SORTBY( tContacts, XLOOKUP( tContacts[source], tSrcPri[SortOrder], tSrcPri[SortKey] ) ),
       XLOOKUP( lookupVal, INDEX( sTable, , 1 ),INDEX( sTable, , 2 ) ) )

您可以看到唯一的区别是 sTable 现在由 tContacts 源的 XLOOKUP 针对 tSrcPrio 进行排序，以返回符合您的优先级规则的数字 SortKey。通过这种方式，sTable 被扩展为包含一组丰富的优先级，而不仅仅是“直接或不直接”。

sTable 扩展

比较

现在我们可以并排放置所有方法，您可以看到它们的结果：

比较

您现在可以在 email3 中看到 XIS 和 XISt 之间的区别。

笔记

§ 由于您可能会多次应用此公式，因此您可以通过创建一个包含 sTable 的命名范围来加快计算速度。假设您决定将其命名为_sTable：

在方法 1 中，_sTable 将设置为：

SORTBY( tContacts, -(tContacts[source] = "direct") ).

在方法 2 中，_sTable 将设置为：

SORTBY( tContacts, XLOOKUP( tContacts[source], tSrcPri[SortOrder], tSrcPri[SortKey] ) )

然后您的 XIS 或 XISt 将被简化为：

XLOOKUP( D9, INDEX( _sTable, , 1 ),INDEX( _sTable, , 2 ) )

推荐阅读