Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with MATCH function
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with MATCH function
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 "DoubleZ" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with MATCH function
On Mon, 9 Mar 2009 10:49:02 -0700, DoubleZ
wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with MATCH function
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 "francis" wrote: 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 "DoubleZ" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with MATCH function
On Mon, 09 Mar 2009 14:25:31 -0400, Ron Rosenfeld
wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with MATCH function
Thank you Ron. That is a very clever formula and I'm sure I'll use it a lot
in the future. "Ron Rosenfeld" wrote: On Mon, 9 Mar 2009 10:49:02 -0700, DoubleZ wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with MATCH () function . . . | Excel Worksheet Functions | |||
Problem with using match function | Excel Worksheet Functions | |||
Match function problem | Excel Worksheet Functions | |||
MATCH function problem | Excel Discussion (Misc queries) | |||
Problem with match function | Excel Discussion (Misc queries) |