☰

双重查找（Excel 公式）

tag-icon • tag-icon microsoft-excel
双重查找（Excel 公式）

我从 Great Plains 导出了超过 20,000 行的数据。我只能导出到制表符分隔文件，但这不是最干净的。我需要一个公式，我将在另一个工作表上使用它来查找 B 列中的 GL 代码（即 10010-6960-900），然后在 A 列中找到该 GL 下的月份（即 1 月），如果这两个条件都满足，则返回该月份 D 列中的值。包括导出的图像。

问题是 GL（即 10010-6960-900）在 B 列中只出现一次，然后是月份。然后是下一个 GL 和另一组月份。如果 GL 在导出时在每个月份旁边重复出现，那就没有问题了，这将是一个简单的 SUMIFS 公式。但我无法让 Excel 在 B 列中找到 GL，然后找到下一个出现的月份并返回金额。

因此，为了尽可能具体，下图中需要公式在 B 列（黄色突出显示）中找到 10010-6960-900，然后在 A 列中找到我需要的月份（绿色突出显示），并从 D 列返回该月份的值（蓝色突出显示）。

在此处输入图片描述

答案1

就像是：

=INDEX(D$9:D$100,MATCH("10010-6960-900",B$9:B$100) + MATCH("January:",A$10:A$21,0)

应该按照你描述的那样做

答案2

双重查找（Excel 公式）

旧办公室

H8下图单元格中的公式为：

=INDEX(INDEX($D$9:$D$34,MATCH($F8,$B$9:$B$34,0)+1):$D$34,
    MATCH($G8,INDEX($A$9:$A$34,MATCH($F8,$B$9:$B$34,0)+1):$A$34,0))

调整34和单元格F8和G8或使用硬编码值。

得出解决方案的公式是：

L8: =B9:B34
M8: =MATCH(F8,B9:B34,0)
N8: =INDEX(A9:A34,MATCH(F8,B9:B34,0)+1)
O8: =INDEX(A9:A34,MATCH(F8,B9:B34,0)+1):A34
P8: =INDEX(D9:D34,MATCH(F8,B9:B34,0)+1):D34

请注意，如果您没有 Microsoft 365，它们将不会溢出。

在此处输入图片描述

Microsoft 365

  • 以下公式使用了相同的想法。我想看看它如何（如果）可以与函数一起使用LET，最后与LAMBDA函数一起使用，即可能会有更好的解决方案。
  • 公式不会溢出，它们会返回单一结果，即如果您想使用它们，您需要将它们复制下来，如上面的屏幕截图所示。

LET

=LET(Data,$A$9:$D$34,lVal,$F8,lValBelow,$G8,lCol,2,lColBelow,1,rColBelow,4,lrOffset,1,
    lData,INDEX(Data,,lCol),lDataB,INDEX(Data,,lColBelow),rDataB,INDEX(Data,,rColBelow),
    lrIndex,XMATCH(lVal,lData)+lrOffset,
    lResult,INDEX(lDataB,lrIndex):TAKE(lDataB,-1),rResult,INDEX(rDataB,lrIndex):TAKE(rDataB,-1),
XLOOKUP(lValBelow,lResult,rResult,""))

LAMBDA

=LAMBDA(Data,lVal,lValBelow,lCol,lColBelow,rColBelow,[lRowOffset],LET(
    lData,INDEX(Data,,lCol),lDataB,INDEX(Data,,lColBelow),rDataB,INDEX(Data,,rColBelow),
    lrOffset,IF(ISOMITTED(lRowOffset),1,lRowOffset),lrIndex,XMATCH(lVal,lData)+lrOffset,
    lResult,INDEX(lDataB,lrIndex):TAKE(lDataB,-1),rResult,INDEX(rDataB,lrIndex):TAKE(rDataB,-1),
XLOOKUP(lValBelow,lResult,rResult,"")))($A$9:$D$34,$F10,$G10,2,1,4,1)

函数（定义名称）

DoubleLookup您可以通过将以下公式分配给名称
( )来创建函数 ( ) Formulas->Name Manager->New：

=LAMBDA(Data,lVal,lValBelow,lCol,lColBelow,rColBelow,[lRowOffset],LET(
    lData,INDEX(Data,,lCol),lDataB,INDEX(Data,,lColBelow),rDataB,INDEX(Data,,rColBelow),
    lrOffset,IF(ISOMITTED(lRowOffset),1,lRowOffset),lrIndex,XMATCH(lVal,lData)+lrOffset,
    lResult,INDEX(lDataB,lrIndex):TAKE(lDataB,-1),rResult,INDEX(rDataB,lrIndex):TAKE(rDataB,-1),
XLOOKUP(lValBelow,lResult,rResult,"")))

然后，您可以使用以下方法在工作簿中使用带有各种参数的函数：

=DoubleLookup($A$9:$D$34,$F8,$G8,2,1,4,1)
  • 请注意，最后一个参数（lRowOffset）是可选的，默认情况下等于1，因此您可以省略它。该参数指定第一个匹配的行与查找第二个匹配的范围的起始行之间的偏移量。

相关内容