One of the cardinal rules of Excel is don’t key-in a value into a formula if that value could change. Tracking the value down could be problematic if you need to change the value. Tables can be the solution to avoiding keyed-in values.
Excel has functions that can handle multiple criteria like IF and CHOOSE. In general these functions don’t use tables and in many cases people use them as an alternative to tables.
Let’s take a simple example. Let’s say we have an input cell A1. If it contains a 1 we will use 10% in a calculation; if it contains 2 we use 15% and if it contains 3 we will use 25%.
Since I used the word “if” in the description you could think that the IF function is the solution.
Something like this may be created.
=IF(A1=1,0.1,IF(A1=2,0.15,IF(A1=3,0.25,0)))*A2
Alternatively you may prefer the elegance of the CHOOSE function which is shorter.
=CHOOSE(A1,0.1,0.15,0.25)*A2
The CHOOSE solution will display an error if an invalid entry is made in A1. You should add the IFERROR function to handle errors.
=IFERROR(CHOOSE(A1,0.1,0.15,0.25),0)*A2
These are both standalone solutions. No other range is needed since all the values are in the formulas.
Now let’s examine two issues that may/will arise.
- We change one of the rates for options 1, 2 or 3 or all three.
In this case we need to identify the formulas that use the rates and manually amend them. Using find and replace could be problematic. - We need to add another two options 4 and 5 to the formulas.
This means adding two extra IF functions to the IF formula, or a smaller change is required to the CHOOSE function – add two extra options.
In both cases we need to amend the formulas to handle the changes.
A better solution
To seamlessly handle both of the above changes we could use a formatted table and a VLOOKUP function (we could also use an INDEX-MATCH combination but that’s another discussion).
The first issue (changes in the percentages) is easily fixed by updating the table with the new values – that’s it, formulas will be automatically updated.
The second issue (new codes) is handled by one of the features of formatted tables. They automatically expand as data is added to them. Adding options 4 and 5 to the bottom of the formatted table will automatically include them in the VLOOKUP formula.
The formula that is a more flexible solution is
=VLOOKUP(A1,tblData,2,0)*A2
This assumes there is a valid entry in A1. Since A1 may be blank or have an invalid entry the following formula is the final solution.
=IFERROR(VLOOKUP(A1,tblData,2,0),0)*A2
The tblData reference is the name of the formatted table. I use the tbl prefix for my table names as it easily differentiates them from range names which I also use frequently.
The table itself can be anywhere in the file and looks like this before the change.
As you can see adding new codes extends the table automatically.
You can name the table in the far-left side of the Design ribbon tab when the table is selected – see image below.
Formatted tables offer the best way to handle options that change. I have written two detailed posts of formatted tables below – the second includes a video.
More recently I wrote an article on the topic for the CPA Australia magazine INTHEBLACK. The article and companion video are at the link below.
Related Posts
Please note: I reserve the right to delete comments that are offensive or off-topic.