使用INDEX/MATCH进行三维查找 [英] Three Dimensional Lookup Using INDEX/MATCH

问题描述

此问题已从已删除的问题中进行了略微改进，

对于那些可以看到已删除帖子的人，它是从此处获取的:

For those who can see deleted posts, it was taken from here: https://stackoverflow.com/questions/39793322/three-dimensional-lookup-no-concatenate-or-named-ranges-excel

我正在尝试在没有命名范围或连接的情况下进行三维查找.简化后，我的数据格式为:

I'm trying to do a three dimensional lookup without named ranges or concatenates. Simplified, my data is on the form:

    Column1 Column2 Column3
Scott           
P   1       2       3
M   4       5       6
N   7       8       9
George          
P   10      11      12
M   13      14      15
N   16      17      18

我现在想搜索一个特定的名称，然后在该名称表中查找一个特定的字母，然后我想将此行号与特定的列进行匹配.

I now want to search for a specific Name and then for a specific letter within that names table, I then want to match this row number with a specific column.

我尝试了一个简单的INDEX/MATCH:

I tried a simple INDEX/MATCH:

=INDEX(A:D,MATCH("M",A:A,0),MATCH("Column1",1:1,0))

这适用于拳头名称，但不适用于其他任何名称，因为它找到了M的第一个实例.

And that works for the fist name but not any others as it finds the first instance of M.

如何修改它以查找其他名称?

How do I modify it to look for a different name?

我已经在下面回答了，但是想看看是否有人有更好的解决方案.

I have answered below, but want to see if someone has a better solution.

推荐答案

我使用了IF()语句array公式来查找P行之后的P行号...我也需要使用MIN()函数获取名称后的第一个P行号.

I used an IF() statement array formula to find what the P row number was after the George row... I also needed to use the MIN() function to get the first P row number after the name.

除此之外，它是一个简单的INDEX()函数....使我的大脑沉迷了一个多小时:).

Beyond that, it's a simple INDEX() function.... that racked my brain for over an hour :).

=INDEX($A$1:$D$9,MIN(IF((ROW(A1:A9)>MATCH($F$4,A1:A9,0))*(A1:A9=$F$5),ROW(A1:A9),"")),MATCH($F$6,$A$1:$D$1,0))

别忘了！
在完成公式时使用Ctrl+Shift+Enter，因此它会作为array公式进行评估.

Don't Forget!
Use Ctrl+Shift+Enter when finishing the formula, so it gets evaluated as an array formula.

这篇关于使用INDEX/MATCH进行三维查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！