☰

Excel - 在一列中查找完全匹配，在另一列中查找完全匹配或下一个更高的匹配

tag-icon • tag-icon microsoft-excel
Excel - 在一列中查找完全匹配，在另一列中查找完全匹配或下一个更高的匹配

问题

我在 Excel 中有一个三列查找表，如下所示：

图1

图 1.查找表。

给定第一个输入查找值（在单元格中输入）F1，该值可以是任何字母数字字符串，并且必须与查找表第一列中的任何值（W、X、Y 或 Z）完全相等；并给定第二个输入查找值（在单元格中输入），F2该值可以是任意数字，并且必须介于查找表第二列中的最小数字和最大数字之间（分别为 1 和 25）。我想要在查找表的第三列中找到满足以下条件并显示在单元格中的相应输出值F3：

  1. 与输出值同一行的第一列上的值等于第一个输入值。换句话说，第一列必须完全匹配。
  2. 与输出值位于同一行的第二列上的值等于或大于第二个输入值。换句话说，匹配必须与第二列完全匹配或为下一个较大的值。

此外，我希望该公式能够满足以下条件：

  1. 不需要Ctrl++ Shift。Enter​
  2. 不需要 Excel 365，但适用于 Excel 2019/2016。
  3. 不要求表值按升序或降序排列，任意一种或都不按升序排列都可以。

例子

第一个例子。假设输入查找值为Y（对于第一列） 和20（对于第二列），则输出应为I，因为输出值的行在第一列和第二列上I都有。Y20

图 2

图 2. 第一个例子（与第二个输入查找值完全匹配）与预期输出。

第二个示例。假设输入查找值为X（对于第一列）和7（对于第二列），则输出应为E，因为具有输出值的行在第一列上E具有，并且其值比第二列上的值大（即）。X710

图 3

图 3.第二个示例（第二个输入查找值的下一个更高匹配）具有预期输出。

我的尝试

为了限制用户可以为第一个和第二个输入查找值选择的可用值，我可以创建数据验证单元格。我知道如何做到这一点，这有点不相关；我对查找输出值的公式更感兴趣。我有以下公式：

=INDEX(C2:C13, MATCH(1, INDEX((A2:A13 = F1) * (B2:B13 >= F2),),0))

该公式满足第一和第二个要求，但不满足第三个要求：当第一列和第二列按升序排列时，该公式有效，但当第二列按降序排列时，该公式无效。如下图所示。

图 4

图 4. 使用我的公式的第一个示例，两个输入列均按升序排列。成功（输出为I）。

图 5

图 5. 使用我的公式的第二个示例，两个输入列均按升序排列。成功（输出为E）。

图 6

图 6. 使用我的公式的第二个示例，其中第二个输入列按降序排列。失败（输出应为E）。

答案1

如果不借助于O365，并且考虑到您希望避免使用需要的公式CTRL+SHIFT+ENTER，这将需要相当冗长的构造，例如：

=IF(COUNTIFS(A2:A13,F1,B2:B13,">="&F2),INDEX(C2:C13,MATCH(AGGREGATE(15,6,B2:B13/((A2:A13=F1)*(B2:B13>=F2)),1),INDEX(B2:B13/((A2:A13=F1)*(B2:B13>=F2)),),0)),"No Result")

尽管不太容易理解，但下面是一种更简洁且更不耗费资源的替代方案：

=IF(COUNTIFS(A2:A13,F1,B2:B13,">="&F2),LOOKUP(1,0/FREQUENCY(0,(0.5+(B2:B13-F2))*8^8^(A2:A13<>F1)),C2:C13),"No Result")

F2但请注意，与第一个解决方案不同，如果和中的条目B2:B13并非全部都是整数，则第二个解决方案可能会失败。

初始COUNTIFS子句用于首先确定是否确实存在符合您的条件的行。

答案2

您可以使用如下公式实现此目的。（其中我有常规单元格地址，而不是表格引用。可以根据需要进行替换。）

=XLOOKUP(F2,  INDEX(  FILTER(A2:C13,A2:A13=F1), , 2),  INDEX(  FILTER(A2:C13,A2:A13=F1), , 3),,  1)

负责FILTER收集第一个查找项，并将 设置为精确。然后，XLOOKUP使用部分的结果处理“匹配或下一个更大的”查找FILTER。INDEX用在中间，也就是说，包装结果FILTER以提供XLOOKUP要查看的数据列。

但是，还有其他策略可以执行步骤INDEX。例如，可以FILTER对数据执行但只返回所需的列。可能更好，因为它可以让人们不必考虑可能改变的表结构。我按照第一种方式设置它，因为你有一个表，人们不会首先考虑它们改变结构，但是......他们可以。所以可能更好的是：

=XLOOKUP(F2,  FILTER(B2:B13,A2:A13=F1),  FILTER(C2:C13,A2:A13=F1),,  1)

相关内容