1

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?

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

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

  • 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). – Andrew Hubbs Jan 30 '14 at 17:35
  • 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 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.