=VLOOKUP and =INDEX(MATCH(),MATCH()) are well known formulas, if you need to get some value corresponding to another value from a specific column. The problem comes, when you are having more than one column or row, and you need to locate the first row/column of a given range.
Let’s say that we have the following 2 cases – case 1 and case 2, as in the picture below:
Thus, for Case 1, for every value in B1:D4 you would like to get the value in column A. Thus:
ewould returntest2hwould returntest3awould returntest1
The formulas are in column G. The array formula that does the trick is the following:
|
1 2 |
=INDEX(A1:A4,MATCH(1,MMULT(--(B1:D4=F1),TRANSPOSE(COLUMN(B1:D4)^0)),0)) |
For Case 2, the needed values are on the top of the column. With some fixing of the formula, it looks like this:
|
1 2 |
=INDEX(B6:D6,1,MATCH(1,MMULT(TRANSPOSE(--(B7:D10=F6)),ROW(B7:D10)^0),0)) |
The “Magic” comes from the =MMULT() function, knowing what to transpose and the fact, that each power of 0 is 1. After some evaluations, this is what you get from the function:
which evaluates to {0,1,0} and =MATCH(1,{0,1,0},0) returns 2, which is the correct column of hh. From there with the =INDEX() formula it returns test22.