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!

### Troubleshooting

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.

### Sheet Names

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.

=VLOOKUP(**'Order Form'!**B5,Products!$B$2:$C$6,2,FALSE)

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:

**=VLOOKUP(B5,Products!$B$2:$C$6,2,FALSE)**

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.

o

0:00 Introduction

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

_______________

Good tip Debra...

That is why I just grab the whole column as the look up value instead..."vlookup(Sheet1!A:A,)"

Hi Debra,

I've had this problem for ages with various models and couldn't work out why. I've just tried it on one of the models and it is perfect !

I've not had it with VLOOKUP before, but as you say, it is very bad with index and match.

Thanks for resolving a long standing quandary.

Kind Regards

Paul, you're welcome, and thanks for letting me know that it worked!

Good to know, great tip. Thanks for pointing this out.

I also usually try to grab the whole column as your other reader, but sometimes I'm looking up an .xlsx file against an .xls file and you cannot do the whole column.

Shair i have seen the same problem from old files to new looking up the entire column, I usually name a range instead and not all just enough o test formula and then extend the named range after.

It may be worth noting that "can happen with other functions" is really "can happen with ALL other functions." Common examples would be SUMIFS and those with a likelihood of referencing other worksheets. But even something as simple as a SUM that provides a row total can be thrown off by sorting if that SUM contains an explicit reference to the worksheet on which it lives. I call this the danger of a boastful worksheet, one that unnecessarily states its own name in a formula.

TheSpreadsheetGuru offers ready to use RemoveSameSheetReferences macro on http://www.thespreadsheetguru.com/the-code-vault/remove-unnecessary-sheet-references-from-excel-formulas-vba-macro

is there way to edit the cell which either holds the Vlookup or index function?

Thank you for this. It was really helpful.

It was exactly the problem I had and now it is solved.