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!
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!