By Neil Shorney, Director, Naturally Sales Ltd.
Someone called me the other day with a VLOOKUP problem. We’ll look at that problem today, as it’s common when you first start working with this powerful function.
The lady in question, let’s call her Sarah, had created a VLOOKUP formula to search for a value within her data list which stretched from C1:D1000. She was looking up corresponding values for a list of alpha-numeric codes. Her formula was constructed something like this:
(in cell B2) =VLOOKUP(A2,C1:D1000,2,FALSE)
She found that the formula worked at the top of her data list but in other places returned an error, even though she knew the data was present. Also, when she sorted her list, other values worked instead.
The problem was that she’d neglected to put in her absolute references, and as she dragged the formula down the worksheet, the data list reference increased as well, causing it to look at empty cells. The correct formula should have been:
=VLOOKUP(A2,C$1:D$1000,2,FALSE)
In our Excel training courses, we talk a lot about “future-proofing” our spreadsheets, and this is a classic example. If Sarah’s data list had grown past D1000, she’d have needed to re-work the formula. A best-practice way of writing the same formula would be:
=VLOOKUP(A2,C:D,2,FALSE)
In this case, if more data were added to the list, it would be picked up as the formula is now selecting the full columns.
If you’d like to take a look at this problem, you can download an example here: http://www.nsales.co.uk/excel-examples/Future-proofing-VLOOKUPs.xlsx
No comments:
Post a Comment