If you have trouble viewing this email, please click here. 
The Brode Report  Aug 2016
Hi, 
I created a short video where I describe some unique features of a financial model that I developed for a client that raised over $350 million. You can see it at brodetelecom.com/modeloverview. 
Quick background/refresher on MATCH:
Why do I care about the overuse of MATCH?
The first case I saw used MATCH in the following situation. You need to grab data from somewhere else in the worksheet, which has line items down the rows and months across the columns. This is an extremely common situation. As is most often the case, you’re referencing data from the same month as the cell you’re in. Now, you know the data is in row 44, and you’re in J57 which, as J5 tells you, is Jan17. The formula I saw was:
Whereas I would have used the simple formula
Which certainly wins for simplicity’s sake. But let’s break down that INDEX formula. It’s saying to look at the array from J44:AK44, i.e. the data for every time period and then the “,1” part is saying to use the first row (of our onerow array), and then over a MATCH… number of columns. The MATCH function looks at the list of dates from J5:AK5 and tells you how many columns down the sequence you are, so you go that many columns over to get the data in row 44. Thus we looked up Jan17 in the list of dates and found that it’s the first column. We then went to J44:AK44 and looked in the first column of that: J44. Bingo. The builder of that spreadsheet was worried about grabbing the wrong column and so did that. My preferred solution is to say that if I’m in J, I pretty much know I can reference data in J and it stands out if it’s otherwise. I certainly make sure that I have consistent time structures across pages, e.g. 2022 is in column BH on every page of the model. But to mess that up within a page? C’mon. This is a solution in search of a problem. The second example I have concerns populating a series of months. So picture this: you have input legal costs for each of five years on row 5, labeled as Y1Y5 in columns G:K. In columns N:BK are the 60 months that make up Y1Y5. In row 2 from G:BK has the year (15) and row 3 lists the month names for N:BK. So your goal is to write a formula that grabs the cost from the proper year and divide it by 12. The MATCH formula I saw did this:
In other words, I’m in N5, so I look up to row 2 and see that I’m in Year 1 from the 1 in N2. So I look into G2:K2 and find out where 1 is in that sequence of 15, and I find out it’s the first column. So then I can go to G5:K5 and grab the first one of that sequence, G5. I prefer something simpler:
Translating: From column F, move over N2 columns and grab that number. An added bonus: Excel is blindingly fast at using OFFSET. MATCH certainly has it’s place. I used it regularly when processing lists and matching up different data sets. But for a financial forecasting model? I often find OFFSET to be the better choice.

Connect with me  Share your thoughts  Subscribe  See past issues  www.brodegroup.com 
The Brode Group 
Strategic Financial Consulting  RealWorld Results 
(303) 4443300 