通过多个条件和多种匹配类型进行EXCEL索引匹配? [英] EXCEL index match by multiple criteria AND multiple match types?
问题描述
所以我敢肯定我的语法有误，但这就是这种情况....
So I am sure I just have the syntax wrong but here is the situation....
我有3列的Sheet2:point_name，x，y我有5列的区域列表的Sheet3:area_name，x_start，x_end，y_start，y_end
I have Sheet2 with 3 columns: point_name, x, y I have Sheet3 with a list of areas with 5 columns: area_name, x_start, x_end, y_start, y_end
我正在尝试将每个X和Y坐标与其各自的区域相关联.我知道索引匹配将允许多个条件，但似乎不允许多个匹配类型....
I am trying to associate each X and Y coord with it's respective area. I know a index match will allow for multiple criteria but it seems it will not allow for multiple match types....
=INDEX(Sheet3!A2:A64,MATCH(Sheet2!B2&Sheet2!C2&Sheet2!B2&Sheet2!C2,Sheet3!$C$2:$C$65&Sheet3!$E$2:$E$65&Sheet3!$B$2:$B$65&Sheet3!$D$2:$D$65,-1&-1&1&1))
我遇到的问题(或我认为我遇到的问题)是，虽然匹配将允许多个条件，但不允许多个匹配类型IE -1& 1大于&小于.
The problem I am running into (or I think the problem I am running into) is that while match will allow for multiple criteria, it does not allow for multiple match types IE -1&1 greater than&less than.
也许我只是以错误的方式来做这件事，以便简要回顾一下
Perhaps I am just going about this the wrong way so as a brief recap
在sheet3中，我有一个区域列表和每个区域的各自边界
I have a list of areas and the respective boundaries for each in sheet3
我想
返回Sheet3的Area_name，其中
return the Sheet3 area_name where
sheet2 x大于sheet3 x_end和
sheet2 x is greater than sheet3 x_end And
sheet2 x小于sheet3 x_start和
sheet2 x less than sheet3 x_start And
sheet2 y大于sheet3 y_end和
sheet2 y greater than sheet3 y_end And
sheet2 y小于sheet3 y_start
sheet2 y less than sheet3 y_start
会更好，但是在获得想要的结果之前，我不想使其复杂化.
in actuality <= >= to would be better but I don't want to complicate it until a get the result I am looking for.
我是否使用索引匹配功能将错误的树吠叫?有没有一种更好的方法可以弥补我的失踪?
Am I barking up the wrong tree with an index match function? Is there a better way to do it that I am missing?
如果这样做是更好的方法，我不反对在sheet2中的foreach行中使用宏.
I am not opposed to using a macro with a foreach row in sheet2 if that would be a better way to do it.
感谢您的时间.
编辑
样本数据:
Sheet2
point_name __x_ __y_
point1 1060 6090
point2 1200 6080
point3 1232 5750
Sheet3
area_name y_start y_end x_start x_end
Area1 6106 6080 1149 1055
Area2 6106 6080 1315 1163
Area3 6227.5 6115.5 1115 1095
Area4 5860 5730 1239 1229
推荐答案
使用您提供的示例数据，结果将为"Area1"，"Area2"和"Area4".使用以下公式可以达到这些结果:
Using your provided sample data, the results would be "Area1", "Area2", and "Area4". Those results are achieved using this formula:
=INDEX(Sheet3!$A$2:$A$5,MATCH(1,INDEX((Sheet3!$D$2:$D$5>=B2)*(Sheet3!$E$2:$E$5<=B2)*(Sheet3!$B$2:$B$5>=C2)*(Sheet3!$C$2:$C$5<=C2),),0))
这篇关于通过多个条件和多种匹配类型进行EXCEL索引匹配?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
