@ > Home > Contents > MATCH in an array, returning row+column Excel 97+

Problem

Many functions return values, like VLOOKUP, INDEX, MAX and SMALL. There are few returning cell references or relative positions, like MATCH. But often these are required, as for multiple VLOOKUPs. 

You do not need an array VLOOKUP, because it would lack the offset property. You can not have other data next to the search data in an array. =SUMPRODUCT(--(A:Z=searchvalue))>0 would be sufficient then.

Different from that, an array MATCH could refer to a corresponding reference within a second array.

Solution (only numbers are allowed, but you may consult 5061.htm)

Name the array, lets say =$A$1:$Y$99, as M. This shortens the formulas, since it is used often, and gives a central place of changes which is an safety argument. A nice trade is ROW($1:$99) and COLUMN($A:$Y) being fully working as ROW(M) and COLUMN(M) as well. {=Formula} means that formulas must be entered by Ctrl-Shift-Enter as array formulas. Otherwise they do not work.

To simplify understanding, we search for the constant 3 within M first rather than for a reference or an expression. The result will be a relative position to the first cell of M. Stay outside M to avoid circular references!

N1 Z1: {=MIN(((M<>3)*2^15+(M=3))*COLUMN(M))} 
returns the first column containing the value 3, like MATCH(...,...,FALSE) would do with the first appearance in a vector. 2^14 is the count of columns in xl2007, so 2^15 puts values <>3 outside.

N2 Z1: {=MIN(((M<3)*2^15+(M>=3))*COLUMN(M))} 
again works as MATCH(...;...) respectively would, with FALSE omitted.

N3 Z1: {=MAX((M=3)*COLUMN(M))}
returns the last column containing a 3. If the value is unique, the result equals the result of formula N 1. As in N2, the operator can be altered to ">=3" or any other.

N4 Z2: {=MATCH(3,OFFSET(M,,Z1-1,,1),FALSE)}
uses the column (of N1, 2 or 3) for matching the exact 3. 

N5 Z2: {=MATCH(3,OFFSET(M,,Z1-1,,1))}
changes the first appearance's condition - differently from N4 - to ">=3".

N6 Z2: {=MAX((OFFSET(M,,Z1-1,,1)=3)*ROW(M))} 
is like MATCH but coming from the bottom side. This could be done with MIN as well, but MATCH performs better as it stops at the first hit.

Using N 1-3 and 4-6, you get 9 combinations. By searching the row first and using this for a match of the column secondly, you get different results, using the following formulas transposed and equivalent to N1+4:

N7 Z1: {=MIN(((M<>3)*2^21+(M=3))*ROW(M))} 

N8 Z2: {=MATCH(3;OFFSET(M,Z1-1,,1,),FALSE)}

Now let us combine N1+4 to a cell reference like R1C1 or A1:

N9 
{="R"&MATCH(3;OFFSET(M,,
MIN(((M<>3)*2^15+(M=3))*COLUMN(M))-1,,1),)&"C"&
MIN(((M<>3)*2^15+(M=3))*COLUMN(M))}

or N10
{=ADDRESS(MATCH(3;OFFSET(M,,
MIN(((M<>3)*2^15+(M=3))*COLUMN(M))-1,,1),),
MIN(((M<>3)*2^15+(M=3))*COLUMN(M)),4,)}

Now let us complicate it away from the constant 3 to the expression MAX((MOD(ROW(M),11)=0)*M) which will look for the a) maximum value b) only chosen out of rows being multiples of 11. See N10 here:

{=ADDRESS(MATCH(MAX((MOD(ROW(M),11)=0)*M);OFFSET(M,,
MIN(((M<>MAX((MOD(ROW(M),11)=0)*M))*2^15+(M=MAX((MOD(ROW(M),11)=0)*M)))*COLUMN(M))-1,,1),),
MIN(((M<>MAX((MOD(ROW(M),11)=0)*M))*2^15+(M=MAX((MOD(ROW(M),11)=0)*M)))*COLUMN(M)),4,)}

Kill this "monster" by replacing repeated parts with named formulas
P: =MAX((MOD(ROW(M),11)=0)*M)
N: =MIN(((M<>P)*2^15+(M=P))*COLUMN(M))
having the very short

{=ADDRESS(MATCH((P,OFFSET(M,,N-1,,1),),N,4,)}