excel - 在列中查找匹配值，然后在行中查找匹配值

Question

这很难在问题标题中解释。我有一个多页 Excel 文件，需要在其中进行查找。Sheet1 包含我要查找的值，sheet2 包含我要查找的位置他们。

在 sheet1 上，我在 E 列中有一个 Service Code，在 列中有一个 Unit Cost J.

然后在 sheet2 上，我在 A 列中列出了服务代码，并在 D 列中列出了相应的价格: N。

我需要找到服务代码，然后返回匹配的单位成本(如果有)。如果没有匹配的单位成本，我需要知道是否存在位于查找单位成本 0.01 范围内的单位成本。

Sheet1 示例

Contract Numner         Revision    Service Code        Unit Cost
441-01568221-BKT        0           RD190A              2.04
441-01568221-BKT        0           PLA10A              156.44
441-01568221-BKT        0           PL156A              312.89
441-01568221-BKT        0           RD190C              2.16

Sheet2 示例

Service Code    CT01    CT02    CT03    CT04    CT05    CT06    CT07    CT08    CT09    CT10    CT11
RD185C          $2.10   $2.10   $2.40   $2.30   $2.00   $2.00   $2.00   $2.00   $2.00   $2.00   $2.00
RD190A          $2.05   $2.05   $2.34   $2.24   $1.95   $1.95   $1.95   $1.95   $1.95   $1.95   $1.95
RD190B          $1.94   $1.94   $2.22   $2.13   $1.85   $1.85   $1.85   $1.85   $1.85   $1.85   $1.85
RD190C          $1.89   $1.89   $2.16   $2.07   $1.80   $1.80   $1.80   $1.80   $1.80   $1.80   $1.80

因此，在此示例中，我将公式放在 O 列中，并希望它显示 Service Code RD190A 没有完全匹配，但 CT01 偏离了 0.01。并表明 服务代码 RD190C 与 CT03 完全匹配。

我尝试过的是:

=INDEX(Sheet2!D2:N2497,MATCH(E5909,Sheet2!A2:A2497,0),MATCH(J5909,"sheet2!$d$"&MATCH(E5909,Sheet2!A2:A2497,0)&":$N$"&MATCH(E5909,Sheet2!A2:A2497,0)),0)

它返回一个错误，指出:

A value used in the formula is of the wrong data type

在 sheet1 上有超过 12000 个 Service Codes:Unit Cost 对，在 sheet2 上只有少于 2500 个唯一服务代码。

编辑

我尝试将其添加到公式中

=INDIRECT("'Sheet2'!$D$"&MATCH(E5909,Sheet2!A2:A2497,0)&":$N$"&MATCH(E5909,Sheet2!A2:A2497,0),"")

但是这不起作用。我可以让它工作:

=INDIRECT("'Sheet2'!$d$"&MATCH(E5909,Sheet2!A2:A2497,0)&"",TRUE)

但它也没有给我我需要的东西。

Answer 1

要获得 ABS 最小差异，

=AGGREGATE(15, 6, ABS(J2-INDEX(Sheet11!D:N, MATCH(E2, Sheet11!A:A, 0), 0)), 1)

要获得 RD190A 的 CT-01 和 RD190C 的 CT-03，

=INDEX(Sheet11!$1:$1, AGGREGATE(15, 6, COLUMN(D:N)/(ABS(J2-INDEX(Sheet11!D:N, MATCH(E2, Sheet11!A:A, 0), 0))=AGGREGATE(15, 6, ABS(J2-INDEX(Sheet11!D:N, MATCH(E2, Sheet11!A:A, 0), 0)), 1)), 1))

首先获得最小的ABS(即绝对值)定价差异。接下来，在类似的包装函数中使用该数字来检索 CT-xx 编号。