jkpads.com Newsletter for October 2015
A Faster Model by getting rid of VLOOKUP
Does your model require calculation set to manual because it takes far too long to recalc? Is your model hard to maintain because you have many VLOOKUP formulas and the column numbers get out of sync? Let me show you a solution to these two problems.
Suppose we have two tables. The top one contains your customer data; the bottom one has invoice data. You need the customer name, address and city alongside the invoice data:
You'd probably use the VLOOKUP function for this, like so:
This has a number of problems:
 The column numbers need to be manually adjusted if you copy the formula to the right
 The column numbers need to be edited if you change the structure of the top table
 The VLOOKUP function is looking up the same index number three times in a row
 The lookup column must be the leftmost column of the columns you are interested in.
Let's solve all of these issues in one go. First, lets do the looking up of the ID once by inserting a column:
So we've used the MATCH function, which returns the row number where it found a match, counting from the first row we've selected as its second argument. This "row index" is subsequently used as the row index argument of the INDEX function:
Notice the dollar signs, allowing me to copy that formula to the right. Since all arguments of the formula are now proper cell references, any change I make to the topmost table, like inserting a column, will be automatically reflected in the INDEX formulas. Moreover, for each data row, I only do a lookup once instead of three times!
Common error using SUMIF
In my daily work as an Excel consultant I have noticed a common mistake in the use of the SUMIF function. Suppose we're totalling sales per product. You could use the SUMIF function to do that like so:
Notice the first argument of the SUMIF function here: it refers to the entire table. This makes the SUMIF function calculate really, really slow. It is also the wrong way to use SUMIF, as it should be looking at a single column, like so:
Just by making this correction you may save as much as 50 percent calculation time!
Excel Events, courses
Stay tuned, we'll be announcing the dates for our famous Amsterdam Excel Summit soon!

