# Excel – Get Top Row of a Multi Row / Mult Column Range

=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:

• `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:

For Case 2, the needed values are on the top of the column. With some fixing of the formula, it looks like this:

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

Tagged with: , , ,