Newsletter by

View this email in your browser 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 left-most 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!

Auditing of Formulas made easy

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Mission-critical Excel Model Crashes

Do you have mission-critical Excel files that cause problems? Consider our Excel File Remediation Utility

Nederlandse cursus: Excel VBA voor Financials

Kost uw maandrapportage u vele uren saai en repeterend werk? Dan is het de hoogste tijd voor onze cursus Excel VBA voor Financials!.
Copyright © 2015 JKP Application Development Services, All rights reserved.

unsubscribe from this list    update subscription preferences 

Email Marketing Powered by Mailchimp