I had a little tussle with Excel. Just what you need on a chilly Monday morning when you have stacks to do.
I learned (again) that if you want to know how to use Excel, the “help” inside the program is not always the best place to look.
I think it’s fair to say that I am a power user of Excel. I’ve given Excel training courses up to advanced level, to audiences of several nationalities. No, really!
But sometimes, I find myself completely mystified, frustrated and downhearted.
Take a look at this:
It’s the contextual help for the function called “MATCH”, specifically for the third argument of that function, called “Match_type”.
So Excel 2016 tells us that Match_type is a number, and must be set to 1, 0 or -1. Fine, I have no issue with that. But then Microsoft goes on to say that when we set 1, 0 or -1 we are “indicating which value to return”.
So if I put “1” as the Match_type, what happens precisely? Who can say? If I put zero, then what? Microsoft isn’t telling us. And heaven forbid if I have the audacity to put “-1”. Perhaps the laptop will explode!
This is Excel version 2016. Excel has been around a long time. This function has been in Excel for a long time. How can it be, when this is such a prominent product, used by millions around the world, with a development budget that dwarfs the GDPs of several smaller countries, that they can miss basic things like this?
I ended up doing what I usually do, when frustrated by useless contextual help: I searched the internet for the answer, and quickly found it in the very first web page at the top of the results. I do this so often that it makes me wonder if Microsoft shouldn’t just remove all the built-in help and stop wasting people’s time with it.
Here’s what I found – just for your info in case you get stuck on this:
I found this useful Excel web page which has the explanation that Microsoft “forgot”:
match_type’ Match type is an important thing. It can have three values 1, 0 or -1.
- If ‘match_type’ has a value 1, it means that match function will find a value that is less than or equal to ‘lookup_value’. It can only be applied if the array (‘range’) is sorted in an ascending order.
- If ‘match_type’ has a value 0, it means that match function will find the first value that is equal to the ‘lookup_value’. In this case sorting of array (‘range’) is not important.
- If ‘match_type’ has a value -1, it means that match function will find the smallest value that is greater than or equal to ‘lookup_value’. It can only be applied if the array (‘range’) is sorted in a descending order.
So now we know.
If by any chance you are puzzled by Excel or maybe you have a small business in Shropshire, Cheshire, Shrewsbury or Telford where spreadsheets are growing like Topsy and you need a data bloke to look at your processes and data and get it all under control, well, I’m here for you. Give me a call, please. It will be a welcome diversion from wrestling with Microsoft “help”!Follow Newology: