Use Excel To Find First And Last Occurrences

Excel can perform pretty much any data manipulation you want, but working out which function to use for a given task isn't always obvious. Excel expert Charley Kyd explains how to use formulas for a common but not trivial task: finding the first and last occurrence of a given entry when you've got a list of items matched to specific dates.

Excel has a pair of functions (INDEX and MATCH) which make finding the first occurrence of an item in a dated list easy, but picking out the last occurrence is a lot harder. Kyd explains how you can achieve that by using the SUMPRODUCT function, which works with arrays of data. Hit the blog post for a full explanation (you can easily copy the formula without knowing how it works, but that will make troubleshooting tricky if something goes wrong).

The example shown in the blog makes the technique look a little trivial, since it's easy for a human to pick out the first and last dates in a small list, but it becomes much more useful if you're working with hundreds of entries. It's not a trick you'll need every day, but a useful reference to keep on hand if you often do complex spreadsheet work.

Use SUMPRODUCT to find the last item in an Excel list [Microsoft Excel 2010 Blog]


Be the first to comment on this story!