You can use an Excel formula to pull data from a lookup table – for example, enter a product name, and automatically see its price. Be careful though, or things can go horribly wrong, and you'll end up selling things at the wrong price.
In this example, I used the VLOOKUP function to show what can go wrong. The same thing can happen with other functions too, such as an INDEX/MATCH formula. In fact, its more likely to happen there!
Get the Prices
One of the many useful things that Excel can do is return a value from a lookup table. Usually, I use an INDEX/MATCH formula for that, because it's powerful and flexible. For this example though, I used VLOOKUP, because it's a bit simpler to set up.
To show you what happened, I made a simple order form. I entered product names in column B, and used VLOOKUP in column C to get the product prices. The pricing table is on the Products sheet, and the order form is showing the correct price for each product, as you can see below.
Sort the List
Not content to leave well enough alone, I decided to sort the product names alphabetically. That made the list look a bit nicer, but then I noticed that all the prices were wrong. Yikes!
Instead of showing $25 for the jacket, the price was showing as $20. What had gone wrong?
This is the kind of thing that you could do at the last second, just before sending someone a copy of the workbook, and you might not notice the errors. You boss wouldn't be too pleased if your shirts were accidentally selling at half price!
To do some troubleshooting, I undid the sort, to put the items back in their original order. Next, I added a FORMULATEXT formula beside the table. That let me see the formulas, while still showing the results in column C.
All the formulas correctly referred to the product in the current row – B5, B6, and so on.
However, in addition to that cell reference, Excel had "helpfully" added unnecessary sheet names. That 'Order Form' reference isn't needed, because we're referring to a cell on the active sheet.
Excel automatically includes the sheet name in all references, as soon as you click on a different sheet, while building a formula. That's what I had done, as you can see in the video below. And that's why the problem is even more likely to occur in an INDEX/MATCH formula, if you start by selecting the INDEX range on a different sheet.
When I sorted the list A-Z, those sheet names seemed to confuse Excel. The Dress, now in cell B5, had started out in cell B9, and the formula in C5 continued to point to cell B9. So, the price in cell C5 was for the Sweater, instead of the Dress.
Fixing the Problem
To fix the problem, I put the list back in its original order (again!), and removed the sheet names from the first reference. Here is the corrected formula:
Then, when I sorted the list, all the formulas referred to the current row, and all the prices were correct.
Watch the Video
To see how things went wrong, and how I fixed the problem, watch this short video. The timeline is shown below the video.
You can go to my website and download the sample file, to follow along.
0:32 Create a VLOOKUP Formula
1:45 Check the Formula
2:08 Show the Formulas
2:47 Sort the List
3:16 Troubleshoot the Formula
3:46 What Caused the Problem
4:34 Fix the Problem
5:24 Get the Sample File