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.


  • Did you find my other answer to your previous question helpful? If yes, then please mark as such. Jan 30 '14 at 10:09
  • Was my answer of use? Dec 6 '14 at 17:07

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.


  • Ah, you are right about the 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). Jan 30 '14 at 17:35
  • 1
    Correct me if I'm wrong, but for an exact match (case sensitive) it seems setting the match parameter to 0 is broken. See the attached picture and linked spreadsheet: i.imgur.com/bfVFZeo.png docs.google.com/spreadsheets/d/…
    – JDG
    Jan 27 '19 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.