Many users of Excel will be familiar with the conditional “IF” function – the plain English syntax of which is:
“If something is true, do this, otherwise do this”
So the formula =IF(A1=1,”Hello”,”Goodbye”) means that if cell A1 contains a 1 then the formula will return the word “Hello”, otherwise (in all other circumstances) you will get “Goodbye”.
This is obviously a little inflexible – what if you wanted some other conditions to return “Hello” as well? You can nest conditions inside each other, as follows:
This has replaced the FALSE outcome of the original formula with another condition, which is a test of whether the cell has a zero in it. If it does, then the result will be “Goodbye”. Failing that, the formula will return “Don’t know”.
So in plain English, this now says “If A1 is 1, display “Hello”; if A1 is 0 (zero), display “Goodbye”, otherwise display “Don’t know”
This is reasonably straightforward (watch out for where all the brackets go, though!) but given that Excel will allow up to 64 nested levels in an IF formula, you can see how it could rapidly become unsustainable.
Happily, there is another option …
The VLOOKUP function
There are actually a few alternatives, but VLOOKUP is probably the favourite as it is relatively easy to use. In plain English the formula says:
“Look up this value in a table or range, and pick the value a certain number of rows to the right”
What this means is that we set up our alternative values as a table – in this case in columns B and C:
Cell A2 contains the VLOOKUP formula =IFERROR(VLOOKUP(A1,B1:C2,2,FALSE),”Don’t know”) which does the following:
It looks up the value found in A1 in the first column (B) of the range B1:C2, and then it returns the value in the second column (C) that corresponds with the value in A1.
FALSE is an optional argument which means that the exact value in A1 must be found in the list, otherwise an error is returned. This argument is outside the scope of this post, so take my word for that bit (or see the post specifically about VLOOKUP here)
Finally, you will note that the whole thing is wrapped in an IFERROR function – this is a way of applying a default value to the VLOOKUP function which, by it’s nature, can only return lookup values when they exist. So this part of the function basically says that if the VLOOKUP returns an error, because the value in A1 is not found in the table, then “Don’t know” will be returned.
This approach seems to be to be easier to understand and maintain than the nested IF calculations. If you wanted to add additional options to the table, then you can easily do so by just typing them in, and remembering to set the range in your formula to the correct number of rows to pick up the new data, so B1:C2 might become B1:C4 for example.