在列中查找匹配值，然后在行中查找 [英] Find a matching value in column then in row

问题描述

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

This is difficult to explain in the question title. I have a multi-page Excel file that I need to do a look up in. Sheet1 has the values that I'm looking for and sheet2 has where I want to find them.

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

On sheet1 I have a Service Code in column E and a Unit Cost in column J.

然后在 sheet2 上，我在 A 列中有一个 Service Code 列表，在 D 列中有相应的价格:N.

Then on sheet2, I have a list of Service Codes in Column A with their corresponding prices in columns D:N.

我需要找到服务代码，然后返回匹配的Unit Cost(如果有).如果没有匹配的Unit Cost，我需要知道是否有Unit Cost 位于查找Unit Cost 的0.01 以内.

I need to find the Service Code and then return the matching Unit Cost if there is one. If there is not a matching Unit Cost I need to know if there is a Unit Cost that is within .01 of the lookup Unit Cost.

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.并证明对于Service Code RD190C，CT03 中存在完全匹配.

So in this example, I put my formula in column O and would like it to show for Service Code RD190A that there are no exact matches but that CT01 is off by .01. And to show that for Service Code RD190C that there is an exact match in 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 个唯一的服务代码.

On sheet1 there are more than 12000 Service Codes:Unit Cost pairs and on sheet2 there are just shy of 2500 unique Service Codes.

编辑

我尝试将其添加到公式中

I have tried adding this to the formula

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

但它不起作用.我可以让它工作:

But it doesn't work. I can get this to work:

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

但它也不能满足我的需求.

But it doesn't get me what I need either.

推荐答案

要获得 ABS 最小差异，

To get the ABS minimum difference,

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

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

To get CT-01 for RD190A and CT-03 for RD190C,

=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 编号.

First get the smallest ABS (i.e. absolute value) difference in pricing. Next use that figure in a similar wrapping function to retrieve the CT-xx number.

这篇关于在列中查找匹配值，然后在行中查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！