Match function returning weird values.

Hello RowlenWilson ,

Note that Match() has three parameters, not just two. The third one is either 0 or 1. When you omit it, it defaults to 1.  

With a 0 as the last parameter, Match looks for a perfect match of the lookup value. If the third parameter is 1 (or omitted), it will look for the first value that is equal to or smaller than the lookup value, but for this option to work properly, the data in the lookup range has to be sorted ascending. If the data is not sorted, the results are pretty unpredictable.

When you use 0 as the last parameter and a perfect match is not found in the data, the formula returns #NA!. But when you use 1 as the last parameter, you will always get some result. If your data is not sorted, that result will most likely be the wrong one.

You can read up on why this matters here: https://teylyn.com/2015/01/15/vlookup-why-do-i-need-true-or-false/  The article is about Vlookup and the fourth parameter, which is either TRUE or FALSE, but the same applies to Match with 1 or 0 as the last parameter.