Excel VLOOKUP Sorting Problem

Excel VLOOKUP Sorting Problem

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!

VLOOKUP Sorting Problem

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.

vlookupsortproblem05

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!

vlookupsortproblem04

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.

vlookupsortproblem01

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.

vlookupsortproblem02

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.

vlookupsortproblem06

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

_______________

VLOOKUP Sorting Problem

25 thoughts on “Excel VLOOKUP Sorting Problem”

  1. Incredibly helpful. I had no idea why my index match formula was scrambling and now I remember sorting at the last minute!! THANK YOU!

  2. THANK YOU SO MUCH!!! I spent so much time trying to figure this out, then I stumbled across your fix!! SO happy you posted this!

  3. Hi,
    Thankyou for the pointer. You explain how to avoid the problem when you sorted data is in the same sheet.

    Is there any way to avoid the problem when the data you are MATCHing on is in another sheet and it IS sorted?

  4. The error you received has nothing to do with the sheet name in the formula. It has everything to do with the sorting of the lookup field, and which value you choose, “True” or “False” within the VLOOKUP function. “False” gives an exact match, and sorting doesn’t matter. “True” can cause issues when sorting data.

    1. The VLOOKUP formula in my example does have FALSE as the last argument, and should return an exact match
      You might see the problem better if you download the sample file, and try it for yourself.

      1. It is kind of weird because the response was correct, the argument “false” should’ve solved it. And it does if the range is in another sheet (the same glitch happens in Google Sheets, but it works if it is in another sheet or even in another workbook). For some reason, if it is on the same sheet it breaks, regardless if the last argument is false or not. So weird that it works like this but not if you import a range. I don’t know…

  5. Holy frick this is incredible. I knew it had something to do with sorting, but I would never have suspected the sheet reference to be the culprit.

  6. thank you so much! i’ve been tearing my hair out over sorting this table with vlookup outputs and it’s finally working after taking out the unnecessary intersheet reference – you’re a lifesaver!

  7. A question Though,
    When you need to use a MATCH function which refers group of data on another Sheet, will the problem of sorting happen?
    I think so, I’ve been struggling to figure what was my problem when sorting, and your example gives me some hints for understanding.

    Now, what is the workaround in my case?
    Is is to copy (the other sheet datas on the one where the MATCH function? (and of course not include the name of the sheet in the cell name).
    JG

  8. Thank You SOOOOOOO MUUUUUCH
    Definetely weird,
    – MATCH + SORT function works fine with reference to cells in another Sheet
    – But doesn’t work on the same sheet when specifying the name of the sheet.
    – Why EXCEL propose the full name of a cell when selecting it in a formula?

    If I didn’t find your post, I would have spend days having generated crap work around.

    Thanks Again.

  9. THANK YOU!! Was driving me crazy trying to find the problem – Would never have thought of this. Crazy this can exist as a problem – of course you want to resort and look at data different ways! You are a life Saver.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.