Other data picking solutions
Recently we were looking at how you could make use of Excel’s logic test as part of a quest to simplify model calculations, and that took us to a list of solutions that could be used to select a piece of data from a row, column or table.
You only need one data picking solution
Many pro modellers use Index. You often get the chance to run a shortened form of Index as shown in cell C22 in the screen shot below. And, when you’re running lots of calculations you also often get the chance to centralise the Match that’s required as an input for Index (C17), which saves Excel work.
If instead you’re in the habit of using something like Vlookup, and you have lots of them, all of those cells will be hunting through rows trying to find a match for the row the piece of data sits in. The Index alternative would see the matching work centralised in one place in one cell (cell C17 in our example).
A fuller list of data picking solutions
Every Excel modeller needs a ‘data picking’ solution close at hand somewhere towards the top of their toolbox. You just need one that you understand and that suits you.
Sometimes you’ll see people doing different things than you to grab hold of a particular data point. You don’t need to worry about that. You’re not necessarily missing anything. It’s likely that you’re just seeing another modeller’s favoured modelling solution. The above list shows some of the alternatives you may sometimes see – they’re many and varied – reflecting the wonderful embarrassment of riches we suffer when we use Excel. There are so many alternative solutions (short and simple is always best)!
Index vs. Vlookup
Although we’ve explained why we’re Index fans, the Vlookup fans are probably going to start jumping up and down shortly. In our example Vlookup is only 22 characters long and requires no Match (at a 20 further characters). But Vlookup ‘haters’ (they do exist) will start jumping up and down too, trying to remind everyone – again – that every single Vlookup requires some matching to go on inside it, which could increase the overall work for the spreadsheet. And they’ll also start saying that the hard-coded number in the back of Vlookup is ugly and might need to be replaced with a Match to make it properly flexible anyway. And Vlookup forces you to lay out data in a particular way, which reduces its flexibility. It’s just the sort of thing Excel boffins could find themselves having a proper fight about after work in the pub on a Friday night!
Shall we try and get some perspective here though? Really, it’s not worth fighting about. You just need something you’re happy with that works for you. Other folks can row their own canoe – that’s fine. No fighting needed – please.
