# Thread: MATCH function strangeness (2000 SR1)

1. ## MATCH function strangeness (2000 SR1)

Supose you have:

A1:40 B1:20 C1:2 D1:1 E1:
A2: B2: 32 C2: 14 D2: E2:
A3: 9 B3: 4 C3: D3: 10 E3:
A4: B4: C4: 5 D4: E4:
A5: B5: C5: D5: E5:

In cell E1, type the following formula:
=MATCH(MAX(A11)+1,A11,1)

With cell E1 selected, grab the fill handle and fill the formula down through cell E4.

In cell A5, type the following formula:
=MATCH(MAX(A1:A4)+1,A1:A4,1)

With cell A5 selected, grab the fill handle and fill right the formula through cell D5.

The resulting worksheet looks similar to the following:

A1: 40 B1: 20 C1: 2 D1: 1 E1: 4
A2: B2: 32 C2: 14 D2: E2: 3
A3: 9 B3: 4 C3: D3: 10 E3: 4
A4: B4: C4: 5 D4: E4: 3
A5: 3 B5: 3 C5: 4 D5: 3 E5:

That is, the cells with the match function returns the position of the last valid (in this case non empty) cell in each row or column.

Note that the first argument in the MATCH function is MAX(...) +1 and that the last argument (the match_type) is 1, so the data in each row or column should be in ascending order to match to work in the normal fashion. But that's is not the case here.

In this case (MATCH used in conjuction with the MAX function with second argument set to 1as mentioned), what you have is that MATCH always return the last valid cell position in the row or column an not, as apparently should be, the cell position that correspond to the largest value that is less than or equal to the first argument as mentioned in the Excel help entry for the MATCH function.

For example in cell C5 we have: MATCH(MAX(C1:C4)+1,C1:C4,1). resolving the first argument, that in turn is equal to MATCH(15,C1:C4,1) . The answer normaly should be 2, the position of cell holding the largest value that is less than or equal to the first argument ( witch is the cell C2 that holds the value 14), and not the 4 the (position of the cell C4 that holds the value 5).

Anyone have an explanation for this? Is this an undocument "feature"?

Go figure.

Replies welcoming.

2. ## Re: MATCH function strangeness (2000 SR1)

The explanation is that you are not following the rules for the function you are using. The Help file says:

<hr>
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. <font color=red>Lookup_array must be
placed in ascending order:</font color=red> ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
<hr>

If you don't follow the rules, you get inconsistent results.

3. ## Changing to Linux

Oh yeah I know, only that in this case you can used MATCH for the purpose of finding the last valid position in a row or column no matter how you sort or arrange the data. And the help files don't mentioned this. Interestingly enough, in the knowledge base there's an article that use the MATCH and MAX function together with the OFFSET function to return the last valid value of rows and columns.

So, I think microsoft should open the code of the worksheet functions ( or make a proxy of them in visual basic) detailing how it is implemented so one could better employ it's potentional. Oh yes, people with vested interest in the industry (API vendors, hello you! ) says that code normaly done by developers should never rely in the implementation. Don't believe them. Who are developers anyway? Ah.. the suckers that fill in the blanks of prefabricated code made by Big guys in the industry believing that's programming.

Otherwise lookout for Linux!

4. ## Re: Changing to Linux

If that is not documented, then it is probably a quirk of how the code is writen and it is not guranteed to do the same thing in different versions of Excel.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•