Getting to know Excel

Microsoft Excel seems to be a mainstay in schools for analysing and querying all kinds of data. Until last year I had barely used Excel since I, myself, was a pupil at school. Having spent years using Matlab to display, manipulate and interrogate data of many different forms.
Getting used to using Excel I’ve found many things that irritate me (not only the fact that it is often used for serious statistics when it shouldn’t be) and I find counterintuitive.

One of these is the behaviour of the LOOKUP function. In the simple example shown below I wish to examine a table of test results sorted alphabetically to find out who scored 56. A quick google suggests LOOKUP as a common way to do this, however as you see this doesn’t work.

It doesn’t work because the vector in which we are searching for 56 is not ordered in ascending order, looking at the next screenshot you can see that sorting the table so that the scores are in ascending order does lead to LOOKUP working

In general I am not going to want to re-sort a table just to do a bit of analysis and then re-sort again at the end – that is just a massive waste of time.

Instead I prefer to combine the INDEX and MATCH functions that Excel provides. These functions do pretty much what you would expect, without any strange behaviour:

  • MATCH returns the location of an item in an array. The first argument is the item we are searching for, the second the array in which we are searching and the third is an optional argument specifying if we are happy to accept a nearest match instead of an exact match.
  • INDEX returns the value at a given location in an array. The first argument gives the array, the second tells which row of the array to return. There is an optional third argument that specifies a column number, for example if I wanted the entry in a table at the intersection of the 2nd row and 3r column.

Combining these two commands we can build a function that does not require data to be sorted into ascending order for the output to be trustworthy. This is shown in the following screen shot


I think I will now very rarely use the LOOKUP function, or VLOOKUP.

As an aside, the screen shots shown are from the iOS version of Excel, which actually has pretty good functionality. I’m not a fan of Microsoft products, being a Mac guy, but the iOS Microsoft Office apps are actually really good. I’m trying to shift to doing a much work as possible on my iPad now (not being able to always find a computer at school in free periods) and I’m sure I will blog about other apps that I find particularly good for the iPad.

3 thoughts on “Getting to know Excel

  1. Excel is also a mainstay in the office environment. As a user of the software for the past 15 years, I’ve come to appreciate much of the work it does. MS Excel when combined with some VBA functionality becomes very powerful. It’s easy for spreadsheet to get large and unwieldy, but once you get the hang of making your own custom functions, the heavy lifting can be done on the back end.

    1. Yep in beginning to appreciate it more and more now, I was always very dismissive of it before, just because I hadn’t used it much and the Mac version used to be awful (especially with macros). Looking forward to getting to know it a bit better, though I’m still going to use Matlab for much more.

  2. I came from a maths degree and a fair bit of matlab use at the time, so it was nice to hear someone else shares my slight annoyances about excel. The fact that most things work as expected but then something as subtle as the example above you mentioned doesn’t work is such a frustration.

    Just don’t get yourself a reputation as someone who knows excel well or else everyone will be bugging you about it!

Leave a Reply

Your email address will not be published. Required fields are marked *