I’m prompted to put finger to keyboard by a recent bit of homework brought back by my daughter. I don’t often do these ‘hints and tips’ type of things, because frankly they are all over the internet, and if you ever need to find out how to do something in Excel, Google tends to be your friend!
However, my daughter came back with a project to build a scoring system which would result in the usual ranking at the end, from ‘Appalling’ to ‘Excellent’ depending on your result. Her ICT teacher had got them building nested IF statements to generate the final outcome, so this sort of thing (in plain English pseudo-code):
IF(SCORE IS BETWEEN 0 AND 5, “APPALLING!”, IF(SCORE IS BETWEEN 6 AND 10, “POOR”, …
and so on – you get the picture. There were five of these nested IFs, to get you from appalling to excellent, via average.
Two things here. First of all, when you have a true/false scenario, as you do with IF statements, you don’t need to test the same condition twice. The ‘between’ logic is unnecessary – all you need to know is that if it is less than 5 it’s appalling, and if it isn’t less than 5, it will pass that condition and go onto the next one in any case. So the pseudo-code could be:
IF(SCORE IS LESS THAN 5, “APPALLING!”, IF(SCORE IS LESS THAN 10, “POOR”, …
So we’re already a bit easier in terms of the syntax. However, a far simpler way of doing this type of thing is to use our old friend VLOOKUP. It’s easy to think of this as a fixed lookup, returning matching values, because that is the most common way of using it.
However, it has a useful argument on the end that makes it very handy for rankings and information of that sort. I’m not going to repeat all the usage information about VLOOKUP – if you want all of that, see what Microsoft have to say about it here. Suffice to say that the syntax is:
VLOOKUP([the lookup value],[the lookup range],[the column you want back],[and a thing called range_lookup])
This range_lookup is the secret to what we need. It has two values, TRUE and FALSE. The default is TRUE, so that’s what you get if you don’t include this argument, and that can cause you some problems because it means that the function will not look for an exact match, but will find the closest match it can prior to the value you are looking for.
So, if you are looking for 7 with the range_lookup set TRUE, in a column that has the values 2, 4, 6, 8 in it, you are going to get back whatever is next to 6. If you set the range_lookup to FALSE, you’ll get an error (#N/A) as the exact value can’t be found in the list.
You need to be careful that the list is sorted as well – if your column read 6, 2, 4, 8 you will still get back whatever was next to the 6, but that may not be what you expect either!
But, as the eagle-eyed will have noticed, this ability is very handy for our bit of ranking homework. All I need to do is knock up a little table that says, for example:0 | Appalling 5 | Poor 10 | Average 15 | Good 20 | Excellent
and then run my VLOOKUP with the range_lookup set to TRUE. Assume my score is 12, and I will get back the value alongside 10, which says ‘Average’ and that’s what I want. Once I hit 15, I’m going to get ‘Good’ returned, but anything between 10 and 14.99 will get me ‘Average’ and all with a formula that is [a] much shorter than five nested IFs and [b] a lot easier to work with. You can readily change the range values, or the lookup text without having to edit the formula and so on.
Seems to me to be the obvious way of dealing with this particular issue, so if you’ve stuck with it this far, I hope this saves you a bit of time at some point in the future! It will definitely save your sanity and eyesight while you try counting the parentheses in the nested IFs to work out where you’ve missed one!