Picking data out of a table

Along with being able to sort data and amalgamate data, often in Excel you'll want to pick a?number out of a big table. Imagine something like a table of customer balances with customer name down the side and time across the top. Imagine you wanted to know a customer's balance at a particular date. That's a regular Excel-kind-of-a challenge.

We love Index

Index will do that perfectly happily for you. We all love Index and the fact that it can pick data out of a table (or rows, or columns) elegantly and effiently explains why it sits so high up in my list of top 10 Excel formulas. All you need to know about data picking is this: just use Index.

Excel gives us too much choice

But one of the ways Excel bewilders us is in presenting us with a huge range of data picking solutions (just use Index). That can be become confusing. You've got Vlookup, Xlookup, SumIf, SumIFS. You also occasionally see people using Offset, Sumproduct or even Indirect to pull data out of a table (just use Index).

The point of listing out the solutions above is just to reassure you. If you see SumIF or Offset all you're seeing is someone else's pet data picking solution. You don't need to worry yourself about the fact that you're not a big Offset or Indirect person (it's probably a good thing).

You only need to be comfortable with one data picking solution whatever that is (use Index). I don't mind if it's Vlookup or Xlookup or whatever (even though you should be using Index and anything else might be the equivalent of turning up to your first day on the job in your new US National Basketball Association team wearing fresh new trainers your Mum just got for you from the supermarket). Just get happy with one. Sail through your career and don't worry when you open someone else's spreadsheet full of whacky Offsets or Sumproducts or something. That's just their data picking solution. You've got yours (hopefully by now it's Index).

Alternative data picking solutions side by side

Here's a spreadsheet showing you how some of the main data picking solutions work. It's all set up so you can see each solution solving the exact same problem side by side. I am hoping the example resonates with someone out there. We're imagining a company budget and what we want to do is pick out the current month's forecast.

Note Index wins as the shortest neatest solution. Was that any surprise?

You can download the example here: Index rocks the house for data picking.

But if your favourite data picking solution makes it onto the list, pat yourself on the back. You've got data picking covered. Play your own game. Don't worry about what everyone else is doing. You're all good. Everyone's loving those trainers you are wearing. Really, they are.