Results 1 to 1 of 1
Thread: Odd MATCH() behavior
2001-03-29, 14:32 #1
- Join Date
- Dec 2000
- NJ, USA
- Thanked 1 Time in 1 Post
Odd MATCH() behavior
Am using the MATCH function with match_type=1, i.e., finding the largest value that is less than or equal to lookup_value. The lookup_array must therefore be in ascending order.
I mistakenly included the column header cell (field name) in the lookup_array, and in my example the value would technically sort to the end of the array. As a result, the lookup_array was NOT in ascending order, and you would expect the MATCH function to return #N/A. However, it almost always works, except for one situation.
If the lookup_value is equal to the first data value in the lookup_array (row 2), then success/failure seems to be determined by the number of rows in the lookup_array!
#rows <= 94: MATCH works OK
95 <= #rows <= 510: MATCH returns #N/A
#rows >= 511: MATCH works OK.
Not a big deal - I don't need to know the answer - but I'm just curious and thought others might be too(?!). Think it must be an Excel bug/quirk. Maybe not a bug, since the documentation states that the lookup_array must be in ascending order.
I wonder why 95 and 511 as cut-off points? Presumably 511 since it's 2^9 - 1. The 95 reminded me of that Excel 95 easter egg where you did something on row 95, but that's probably unrelated.
In my example all cell values all begin with a letter, so it's nothing to do with any mix-up over numerics vs. labels.