Three Dimensional Lookup Using INDEX/MATCH

Question

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.

Answer 1

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.