Problem with MATCH function

[DoubleZ]

In Excel 2007 I am using the MATCH function and trying to return the next
greatest value. For some reason, entering -1 as the match type is returning
a #N/A error. I tried a very simple example and it is still returning the
same error. In cells A1:A10 I have the values 1 through 10 (in ascending
order). In cell B1 I have:
=MATCH(5.7,A1:A10,-1). I would think this should return 6 because it rounds
up to 6 which is in the sixth row of the array.

Please help.

 

[francis]

using -1 requires that the lookup_array must be placed in descending order and
will finds the smallest value that is greater than or equal to lookup_value

if you want to return 6 for 5.7 as lookup value, try
=MATCH(MROUND(5.7,1),A1:A10,0)

Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[Ron Rosenfeld]

In Excel 2007 I am using the MATCH function and trying to return the next
greatest value. For some reason, entering -1 as the match type is returning
a #N/A error. I tried a very simple example and it is still returning the
same error. In cells A1:A10 I have the values 1 through 10 (in ascending
order). In cell B1 I have:
=MATCH(5.7,A1:A10,-1). I would think this should return 6 because it rounds
up to 6 which is in the sixth row of the array.

Please help.

That parameter tells the MATCH function which way the array is sorted.

Here's a different approach to return the value equal to value given or the
next higher value in a field that is sorted ascending:

=LARGE(A1:A10,COUNT(A1:A10)-COUNTIF(A1:A10,"<5.7"))

or, if you put the 5.7 in a cell, so that value can be easily changed:


=LARGE(A1:A10,COUNT(A1:A10)-COUNTIF(A1:A10,"<" &B1))

If you always want to return the next higher value, even when there is an exact
match, then change the "<" to "<="

--ron

 

[DoubleZ]

Thanks Francis. I would have thought that Excel would have mentioned the
descending order a little more clearly. However, it does make sense. Thanks
again for you help.

DoubleZ

 

[Ron Rosenfeld]

That parameter tells the MATCH function which way the array is sorted.

Here's a different approach to return the value equal to value given or the
next higher value in a field that is sorted ascending:

Oops, that was from something else -- the range does not need to be sorted to
use this formula"

=LARGE(A1:A10,COUNT(A1:A10)-COUNTIF(A1:A10,"<5.7"))

or, if you put the 5.7 in a cell, so that value can be easily changed:


=LARGE(A1:A10,COUNT(A1:A10)-COUNTIF(A1:A10,"<" &B1))

If you always want to return the next higher value, even when there is an exact
match, then change the "<" to "<="

--ron

--ron

 

[DoubleZ]

Thank you Ron. That is a very clever formula and I'm sure I'll use it a lot
in the future.