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.