Excel Just Taught Me a Valuable Lesson
The other day, I found out that the exact match in Excel isn’t exact as I thought it was.
Let me explain.
For the MATCH function, there are three different match types:
- 0 - Exact match
- 1 - Exact or next smallest
- -1 Exact or next largest
The thing about the 0 or exact match in this function is that it is not case sensitive. This means that it treat “Apple”, “apple”, “APPLE”, and “APple” all the same when it comes to matching.
This is mostly convenient since case differences in data happen all of the time when someone enters information (I’ve typed in DUane plenty of times to know).
But this isn’t what I expected and THAT is the life lesson.
I’ve been in situations in life where I got exactly what I asked for and somehow it didn’t feel right.
I got what I “WAnteD” but not what I “wanted”.
Sometimes, you need to specify case sensitivity with your asks, dreams, hopes, and ambitions.
Editors Note: Actual Excel Lesson
If you want a case sensitive, exact match using the MATCH function, you can specify it with the following:
=MATCH(TRUE, EXACT(val, lookup_col), 0)
Practically, it looks something like: =MATCH(TRUE, EXACT(F4, B4:B301), 0) and you’ll need to enter it in as an array formula by pressing CTRL + SHIFT + ENTER. You’ll know it’s an array formula because the formula will be wrapped in curly brackets { =FORMULA( ) }.
This is important when using the INDEX and MATCH operations together. See this breakdown for more info.