There is nothing wrong with the average vlookup. But do you really want to be average? The coveted analyst needs exceptional Excel skills. How can we make Vlookup even more accurate and efficient?
Subscribe to the newsletter and get even more VLOOKUP tips with my “Beginner’s Guide to Getting Hired wtih Excel,” yours free for subscribing.
“Can It Scale?”
This is an overused business phrase that means, “can it be done on a much larger scale with fewer resources per output?”
You want your Excel skills to be “scalable” — that is, you should be able to apply actions to large sets of data after applying it to a few cells.
In our example below, we want to know the sales for store 7.
Ideally, you can just drop-and-drag your formula across all these cells. Let’s first try to drag the formula down Column L. We see an error in Cell G3. What’s happening? Place your cursor inside the cell and hit F2 to get an interactive look at your formula.
Check out Row 2: Store 7’s values are outside your lookup table. Your instinct might be to write a new vlookup with this new table array. Fortunately, there is a more “scalable” solution.
Cell anchoring lets you drag all across Excel without changing the cell reference. To check that cell anchoring is on, go to your table array cell reference and hit F4. Dollar signs should appear in your formula.
If you do not see dollar signs when pressing F4, press the Function Lock command on your keyboard.
We are going to modify our table array to prevent these errors. Go into the formula and place dollar signs around your array references by pressing F4.
Cell anchoring will ensure that your cell references do not move around when you copy and paste. You will never miss data now!
Leave a Reply