* =VLOOKUP* and =

*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.*

**INDEX(MATCH(),MATCH())**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:

`e`

would return`test2`

`h`

would return`test3`

`a`

would return`test1`

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`

.