Like many people, I tend to use only the Excel functions that I’m most familiar with to help get the answers I need without too much learning.
A few years ago I learned to use VLookup and drop-down lists, which can be used individually or together to create dynamic spreadsheets for scenario analysis and exploring large data. I applied this to a project where the client wanted to benchmark their local community relative to others in New Zealand around council operating expenses per capita and other measures.
As part of that project, I considered learning how to use and combine the Index and Match functions in Excel. At the time it felt it would be too daunting to learn, as I was only just learning VLookup. In retrospect, it would have involved a similar amount of work and achieved the same result.
I recently undertook a new, relatively small project which caused me to revisit the Index and Match functions. These functions are fairly quick to learn when you focus and have a specific application. I got my head around how it related to my data and how to write a nested Index-Match formula, and voila – a new tool that fulfills the client’s needs.
In this case the client wanted something that allowed them to input new data each month, which would automatically generate a report with summary tables and graphs comparing the most recent month to preceding monthly data and the same month the previous year. By telling the spreadsheet which month they want to report, the tool uses Index-Match for each table to find the right month, match it with other attribute such as geographic area, ethnicity or gender, and all the calculations flow automatically through to a final Report which is set up to print to PDF. Like magic!
The lesson – if something looks too hard to learn, don’t just turn your back on it. At the very least, make a note and revisit it when you think it may may be relevant to your situation.
paulkillerby 