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

enter image description here

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.

About

VBA Developer

Tagged with: , , ,