"Match" function - erratic behavior in Excel 2007 but not 2003

[smoddelm]

I have a spreadsheet opened in Excel 2007 with the following values (fo
formulas -- values are hard-wired) in cells A3 through A18:

39,814
40,179
40,544
40,909
41,275
41,640
0
0
0
0
0
0
0
0
0
0

Cell A1 has the value 39,814 -- the same as cell A3. In cell C3, I have the
following formula: '=MATCH(A1,A3:A18)." The formula returns the value "16"
-- the total number of entries in A3 - A18. If I shorten the MATCH formula
to include only the values through A14 (or any lesser row), it returns the
value "1" -- the correct answer. Agai, there are zeroes in cells A9 through
A18, so the problem is not something that occurs as soon as the value in
Column A dorps to zero -- it works fine if my range includes anything up to
the first 6 zero values in Column A. If I replace the zero in Cell A9 with
50,000, I get two more rows of "correct" answers (not just one more row).
And if I replace the zero in cell A10 with 60,000, all the answers are
"correct."

I created this to simply illustrate an issue I am having in a large
spreadsheet that I inherited, where to take another approach (than using the
MATCH function) would involve much modification to what the author set up.
So I really need to get MATCH to work right. The large spreadsheet worked
fine in Excel 2003 (and still does). (However, my little example spreadsheet
has the same problem when I open it in 2003 as in 2007!)

I would appreciate any help in figuring out why this happens -- it's drving
me nuts. Thanks.

 

[Bernie Deitrick]

Since your values are not sorted in ascending order, you need to use

=MATCH(A1,A3:A18,FALSE)
which is the same as this:
=MATCH(A1,A3:A18,0)

HTH,
Bernie
MS Excel MVP

 

[smoddelm]

Thanks for the quick reply. I do see that using a blank "match_type" (or 1
or -1) "requires" the lookup array to be in ascending order -- clearly not
the case in my array where there are a number of zeroes after the highest
value. Using the 0/FALSE "match-type" won't work for my situation because
I'm not looking for exact matches (though I had one in my example).

Strange that it worked before, that it still works in Excel 2003 (at least
in my bigger worksheet which is all I'm really concerned about), and that it
works with 6 or fewer zero values after the highest value but no more than 6.

I guess I will either have to continue to run in Excel 2003 or do a little
surgery on how the spreadsheet is set up. Thanks again.

I

 

[Bernie Deitrick]

Sorry, I thought you wanted an exact match.... Try this instead, or a variation on it....

=MATCH(A1,OFFSET(A3,0,0,COUNTIF(A3:A1000,">0")))

HTH,
Bernie
MS Excel MVP

 

[smoddelm]

Clever -- thanks!

Sorry, I thought you wanted an exact match.... Try this instead, or a variation on it....

=MATCH(A1,OFFSET(A3,0,0,COUNTIF(A3:A1000,">0")))

HTH,
Bernie
MS Excel MVP