CODEWITHSUNDEEP
google-sheetsmatch
Elm
Elm

Reputation: 1407

google sheets match function - does not match

In google sheets, I use the match function to find a value A in array Y. The value A appears in array Y. The match function does not see A in Y. I have had this happen often. I try changing the type (eg. multiplying both values of A by 1). Rarely does anything work. Sometimes if I copy and then paste over as value it will work.

Example: Find A in array (A,B,C,D). The match function seemingly randomly sometimes returns 'no match'

I searched google forums and found an unanswered post from 2010:

https://code.google.com/p/gdata-issues/issues/detail?id=2367

Thanks!

Upvotes: 1

Views: 6025

Answers (2)

Gooby
Gooby

Reputation: 70

It's possible that MATCH is assuming your array is sorted. I ran into this problem myself and when I specified the third argument as 0 it worked, but when I didn't MATCH returned the last element of the same data type. For example, for array N {"---",3,5,2,"---"), MATCH(MAX(N),N) returns 4 but MATCH(MAX(N),N,0) returns 3.

Edit: For clarity, argument 3 is a boolean which should be true if the array is sorted, and defaults to true.

Upvotes: 1

Elm
Elm

Reputation: 1407

The answer is the problem lies with Google sheets. There's a date type difference between my search item and the key array. So the solution should be just to make them the same type (eg. plain text). Sometimes this works immediately, sometimes it works the next day and sometimes it never works. I've done this on identical sheet copies with those varying results. Long-short: buggy. That's the case with a lot of the referencing in google sheets (eg. importrange, etc.). But overall still a good product and I use it as my primary spreadsheet platform due mainly to the sharing ability.

If anyone from google would like to add to the conversation it would be welcome.

Upvotes: 1

Related Questions