Google-sheets – MATCH function does not work

google sheets

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?

Best Answer

You need to add the third parameter, of the MATCH formula, explicitly:

Formula

=MATCH(MAX(FILTER(B122:U122, B122:U122<0)),B122:U122, 0)

Explained

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.

Reference

Related Topic