Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.Challenge: I want to future-proof the column number returned in my VLOOKUP formula.
VLOOKUP is a hugely useful formula in Excel. Apart from the basic mathematical operators and the IF statement, there’s nothing that touches it in terms of how often I delve for it in trying to solve a problem.
But it’s a very unstable formula. Here’s its syntax.
VLOOKUP(needle,haystack,column number to return,exact match?)
You look for a number in a haystack of data, and return the nth column across from that haystack.
The issue comes when the range that you’re looking up in is liable to change in structure and size.
The needle is unlikely to ever change, so that’s not a problem. Meanwhile, the haystack is likely to be a fixed range, and if it’s not, it can be dealt with by using a names range. The column number to return is the weak link. If you innocently insert or delete a column from the haystack, it can make your VLOOKUPs come tumbling down.
In the main, I’ve always hard-coded the column reference, not thinking to do things any differently. But recently, I found a way of making it dynamic.
At the head of the column in which you’re looking up, likely in a hidden row, store a value that represents the column number that the column represents in the range. So if your data runs from row 3 to row 100, with column titles in row 2, and the column in which you’re looking up is column A, put the following formula in, say, D1:
=COLUMN(D3)-COLUMN($A$3)+1
The COLUMN function simply returns the column number of the cell being referenced.
In this example, the formula will return the value 4.
Copy this formula across the relevant cells in row 1, and the row can then be hidden.
And in the lookup itself, if you want to bring back column D, use the following formula:
=VLOOKUP(needle,$A$3:$Z$100,D$1,0)
By doing this, if a column is inserted somewhere between columns A and D, the data that was in column D will move to column E, but its header number will increase from 4 to 5. And importantly, your VLOOKUP won’t break.
