问题描述
这在问题标题中很难解释.我有一个需要查找的多页 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 Cost
J
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 Code
s 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)
它返回一个错误说:
公式中使用的值的数据类型错误
在 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.
这篇关于在列中查找匹配值,然后在行中查找的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!