Index Match Row and Column

[kaempfe]

Hi.

I am using Match to find a given value in a column. An example of this equation is : =MATCH(A2,Data!A:A,0) where I'm looking to match the value in A2 within column A in the Data tab.

This is working fine and it returns a row - let's say row 72 for discussion purposes.

Within Row 72, I would like to now search for another value in a set of columns B:M.

I tried using the array for index match but in this case I was trying to find the matching value in columns for multiple rows - see the section "MATCH($Z$2,$B$2:$M$100)" in teh following equation.
=INDEX($B$2:$M$100,MATCH($Z$1,$B$2:$B$100),MATCH($Z$2,$B$2:$M$100))

I used Shift-Ctrl-Enter to set the array but it returns NA so I'm assuming in the second match, I have to pick columns in a specific row and I cannot have a range from B2 to M100.

For my next try, I tried finding the row of the project (Let's say row 72), and was then trying to find the match within columns B:M for row 72.

However, I can't seem to get the syntax.

I can make it work manually by selecting all of row 72:
=MATCH($Z$2,Data!72:72,0)

But how can I make this dynamic (ideally in one formula) so that the formula first finds the appropriate row and then searches that row or searches a specific set of columns in that row for the value in Z2?

Thanks!

 

[Fluff]

You haven't said what you want the formula to return.
But this will return the row 1 value if Z2 value is found on the Z1 value row
=INDEX(Pcode!$B$1:$M$1,MATCH($Z$2,INDEX(Pcode!$B$2:$M$100,MATCH($Z$1,Pcode!$B$2:$B$100,0),0),0))

It's a normal formula & doesn't need Ctrl shift enter

 

[kaempfe]

If I read this correctly....
you are using pcode instead of my Data - OK
I'd like two things now that I realize:
1. I'd like the ADDRESS for the column in which the Z2 value is found in the Z1 value row.
2. I'd like to return the DATA in the cell to the right of the address in #1.

** I believe you were returning the value in row 1 merely bc I didn't tell you want I wanted returned but just in case - Note that the header names in row 1 are irrelevant and cant be used to find a column.

Thanks

 

[kaempfe]

As a follow up, I think I have figured out most if you can help me change from getting data from the "Pcode!$B$1:$M$1" to getting the data from the row that matches Z1 and the column one to the right of the column with the value that matches Z2.

At the very least, how do I swap out "Pcode!$B$1:$M$1" with something that pull the data from the row that matches Z1?

Thanks!

 

[Fluff]

How about
=INDEX(Data!$B$2:$M$100,MATCH($Z$1,Data!$B$2:$B$100,0),MATCH($Z$2,INDEX(Data!$B$2:$M$100,MATCH($Z$1,Data!$B$2:$B$100,0),0),0)+1)