Hello
this should be an easy task for you experts:
I have 2 sheets, in the first sheet I want to lookup values from the second sheet that correspond to 3 variables.
2 of them are in columns, and one in a row.
So I write this formula:
=INDEX('Sheet2'!E3:L300,MATCH(C2,'Sheet2'!B:B,0),MATCH(B10,'Sheet2'!C:C,0),MATCH(E5,'Sheet2'!E1:L1,0))
But I have a #ref result or some random number. When I evaluate the formula, result of the first match shows row 67, second match row 7 and thrid match row 3. Those are matches.
The thing is I want the number that is at the intersection of the three MATCH.
So it is more like a 3 way lookup with index match
Ok i think i found it with this formula:
=INDEX($A$3:$D$11, MATCH(G1&G2,$A$3:$A$11&$B$3:$B$11,0), MATCH(G3,$A$2:$D$2,0))
this should be an easy task for you experts:
I have 2 sheets, in the first sheet I want to lookup values from the second sheet that correspond to 3 variables.
2 of them are in columns, and one in a row.
So I write this formula:
=INDEX('Sheet2'!E3:L300,MATCH(C2,'Sheet2'!B:B,0),MATCH(B10,'Sheet2'!C:C,0),MATCH(E5,'Sheet2'!E1:L1,0))
But I have a #ref result or some random number. When I evaluate the formula, result of the first match shows row 67, second match row 7 and thrid match row 3. Those are matches.
The thing is I want the number that is at the intersection of the three MATCH.
So it is more like a 3 way lookup with index match
Ok i think i found it with this formula:
=INDEX($A$3:$D$11, MATCH(G1&G2,$A$3:$A$11&$B$3:$B$11,0), MATCH(G3,$A$2:$D$2,0))
