Formulas discussed: Lookup, Vlookup, Hlookup, Index, Match.
Just want the table to show you which formula to pick – click here. The workbook used throughout this post is available here.
This article and the attached spreadsheet will discuss the various methods of searching for a data point in an Excel table and return the value in a corresponding row or column, without having to manually look through the table yourself. Common examples include searching for a sales result for a particular period, or matching a data field to a person’s name.
Brendan WalpoleIndex and match versus lookups – when to use them
Styles are a reasonably new feature, being significantly upgraded in Excel 2007 for Windows (earlier verisons had much reduced functionality), but not making it to the Mac version until Excel 2011, inexplicably being left out of Excel 2008 for Mac, along with all macro/VBA functionality. I love styles so much that 10 years ago wrote a .xla file that I used to keep formatting consistent throughout a model (although far inferior to what MS ultimately incorporated, as it was very slow, having to look at each cell individually). And when I found out that they’d been included in Excel 2011, I went and upgraded the whole Office suite on the spot (which if the company you work for is a participant in Microsoft’s Home Use Program, is well worth it).
What are Styles
Styles are a set of preset, editable formats that you can apply throughout your workbook. Excel contains a good number of built-in styles straight out of the box, such as Normal, Output, Heading1, Total, Input etc. These are all capable of being customised to your liking, or you can create new ones.
Brendan WalpoleExcel’s best feature hidden in plain sight – Using Styles