using the match function with ranges

Hi.

First of all, that construction would require coercing (e.g. by some external, array-processing function) in order to produce the necessary array of returns. (Either that or it is entered as a multi-cell array formula over a (vertical) range of - in this case - four cells. However, I will assume that we are dealing with a single formula here for the sake of explanation.)

In essence, though, MATCH is operating in precisely the same way as it always does. The difference is that, instead of returning just a single value, here you return an array of four values. Of course, that array cannot - unlike a single value - be returned to a single cell within the worksheet. Instead it is stored within Excel's memory, ready to be used as desired (by, as mentioned, some external function).

So, for example, with A1:A4 containing "A", "B", "A", "C" respectively, the construction:

=SUMPRODUCT(MATCH(A1:A4,A1:A4,0))

(I chose SUMPRODUCT as a random example of a function which can act to coerce an array of returns.)

will resolve to:

=SUMPRODUCT({1;2;1;4})

i.e. comprising an array whose entries are precisely those which we would have obtained had we calculated the four individual constructions:

=MATCH(A1,A1:A4,0)

i.e. 1

=MATCH(A2,A1:A4,0)

i.e. 2

=MATCH(A3,A1:A4,0)

i.e. 1

=MATCH(A4,A1:A4,0)

i.e. 4

and then somehow combined these results into a single array.

Regards

Originally Posted by stephme55

Hmmm, I sort of get it, but how can A3 be equal to 1? Doesn't it HAVE to be the third value in the match? A1,A2,A3,A4 match A1, A2, A3, A4, is just 1,2,3,4. I'm probably misunderstanding.

its finding the first match of A3. In this case A3 = A and the first match in the array {A,B,A,C} for an A is 1

MATCH always returns the relative position of the first instance of the lookup_value found within the range.

Regards