CODEWITHSUNDEEP
excelexcel-formulamatchexcel-2010
Andrey
Andrey

Reputation: 6367

Why is Match("text", A:A) function not working with text in Excel

Why is this formula not working:

enter image description here

I would expect to get 1 instead of 2.

Upvotes: 1

Views: 934

Answers (2)

Vityata
Vityata

Reputation: 43585

Short story

The correct way to use Match() and Vlookup() is always to write False at the latest optional parameter, in order to get what you need - exact value position or error, if it is not there. Thus:

=MATCH("s",A:A,False) 'or 0 instead of False

Long story

What is False default behaviour? This one as per Match() documentation:

MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.

Back to our question - Why does it not work? Because the default parameter [match_type] is set to 1, which is Less Than or Equal:

enter image description here

  • If you need to use -1 or 1 as optional match_type arguments, then the values must be sorted to return some meaningful insight.

However what is "less than or equal" (the default match_type parameter) in this formula? If you ask Excel this is how it thinks:

="a">"s" 'False
="a"<"s" 'True
="a"="s" 'False

But, it still did something wrong. Why did it started to evaluate and compare with the second value "a" and returned its position, as if starting with the first cell would have fulfilled the "less or equal" condition? This really spices things up, as there are other "hidden" features of Excel coming here - e.g. setting the first cell of a range to be the last to check, which is built-in in some functions.

So, it started to compare with "a", found that it is less or equal and returned its position. Job done!

To completely lose everyone on this one, if you change the formula in the question to =MATCH("s";A1:A2), it will return what you are expecting, thus the first cell being checked last functions depends on the type of the range.

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36870

As @JvdV suggested you have to specify 3rd parameter Match_Type for older versions of excel.

=MATCH("s",A:A,0)

But for Excel365 and Google-Sheets you can omit this parameter and excel will behave with default parameter.

Upvotes: 1

Related Questions