Match function returning weird values.

[RowlenWilson]

I have a list of values in column E (E2:E960) and I have list of values in row P (P2:P379). I want to use the values in E as the source and see in if the values exist in P. So does E2 exist in P2:P379. I wrote "=MATCH(E2,P2:P379)" but I get weird values. "9" appears to be not found. Then as I get further down the values are strange. I get a result of "221" but the value does not exist. Secondly, is it possible to look for  duplicate values between the two columns (maybe that is easier)?

[#2 · Accepted solution]

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.

[#3]

I also found some trailing spaces in my data which affected the results. Your response worked. Thanks. @Ingeborg Hawighorst