使用 INDEX/MATCH 进行三维查找 [英] Three Dimensional Lookup Using INDEX/MATCH
问题描述
这是从已被删除的问题中采取并略有改进
对于那些可以看到已删除帖子的人,摘自这里:
This was taken and improved slightly from Question that has since been deleted
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.
I tried a simple INDEX/MATCH:
=INDEX(A:D,MATCH("M",A:A,0),MATCH("Column1",1:1,0))
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.
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.
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))
Don't Forget!
Use Ctrl+Shift+Enter
when finishing the formula, so it gets evaluated as an array
formula.
这篇关于使用 INDEX/MATCH 进行三维查找的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!