MATCH function does not appear to work at all. Given the following data:
$7,109.19 | $867.19 | -$5,374.81 | -$11,616.81
=MATCH(MAX(FILTER(B122:U122, B122:U122 < 0)),B122:U122)
returns the error:
error: Did not find value -5374.810000000005
How can it not match a field for a value generated purely out of values that exist in the array?
All I really want to do is select a cell in a different row of the same column of the value that should be returned by that match call.
Thoughts?
You need to add the third parameter, of the MATCH formula, explicitly:
=MATCH(MAX(FILTER(B122:U122, B122:U122<0)),B122:U122, 0)
The third parameter of the MATCH will tell it to find an exact match, as stated in the help:
0 indicates exact match, and is required in situations where range is not sorted.
By default it is set at 1, if no value is given. This will yield a completely different result.
FILTER mistake. Unfortunately, that was only an error in the question not my actual use. The MAX portion is working correctly and returning the -$5,374.81 value. What I am unclear about is why the MATCH call is failing to match that value (especially since it is pulled directly from the range I am matching against). I understand how the 3rd parameter is supposed to work, but it does not appear to actually work (with any of the possible values).
– Andrew Hubbs
Jan 30 '14 at 17:35
