在列中然后在行中找到匹配的值 [英] 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，在J列中有一个Unit Cost.
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.
我需要找到Service Code，然后返回匹配的Unit Cost(如果有的话).如果没有匹配的Unit Cost，我需要知道在Unit Cost的.01之内是否存在Unit Cost.
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)
它返回一个错误，指出:
It returns an error saying that:
公式中使用的值是错误的数据类型
A value used in the formula is of the wrong data type
在sheet1上有超过12000个Service Codes:Unit Cost对，在sheet2上只有2500个唯一的Service Codes.
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屋！
