VLOOKUP Hack #1: Sort Order

VLOOKUP is one of the most popular Excel functions. It can do amazing things and help save time. But, there are some really cool hacks that make it even better. This is the first post in a series of VLOOKUP Hacks.

Hope you enjoy 🙂

Issue

VLOOKUP Hack #1 helps address the sort issue. Sort issue? Yes, and the sort issue has confuzzled many an Excel user over the years.

I’ve included a short video demonstration as well as a detailed narrative below for reference.

Video Demonstration

Detailed Narrative

Let’s say we wanted to use VLOOKUP to retrieve an account name based on the account number from a chart of accounts, as shown below.

Assuming the lookup value is B7, the lookup range is Table1, and the account name is in the second column, we could use something like this in C7:

=VLOOKUP(B7,Table1,2)

When the table is sorted in ascending order by the lookup column, the AcctNum column, you get the expected result. For example, if the AcctNum is 1002, the VLOOKUP function above returns the expected account name, Savings, as shown in C7 below.

But, if the table get sorted by AcctName instead, we get an unexpected result, as shown below.

Wait, what? 1002 is not Checking account, yet, that is what VLOOKUP returns … is it broken? Is the formula wrong? What’s up? Do I have to sort by the lookup column? What is going on here?

These questions all bring us to our first hack.

Hack

So, the VLOOKUP formula above is written like this:

=VLOOKUP(B7,Table1,2)

You will notice that 3 arguments are defined, B7, Table1, and 2.

But, here is the hack: there is an optional 4th argument! 

When the 4th argument is omitted, as in the formula above, it takes on a default value. So, we need to unpack this mysterious 4th argument to understand what is happening.

The 4th argument controls the behavior of VLOOKUP in a couple of key ways, including, as you may suspect, the sorting requirement. Officially, the 4th argument is called the range_lookup argument. Practically, it tells Excel whether you are looking for an exact matching value, or, a value between a range of values. It is a Boolean argument, so it expects a TRUE or FALSE value. (Alternatively, you can use 0 instead of FALSE and any non-zero number instead of TRUE.) TRUE means you are looking for a value between a range of values, and FALSE means you are looking for an exact matching value. I’ll expand this idea more in the next post, but for now, I want to stay focused on the sort issue.

When the 4th argument is TRUE, the data must be sorted in ascending order to return an accurate result. However, when the 4th argument is FALSE, the data need not be sorted…any order is fine.

So, when the 4th argument is TRUE, you may get an unexpected result if the data is not sorted in ascending order by the first column in the range. And, that is exactly what we saw above. We got an unexpected result. Instead of 1002 returning Savings, it returned Checking account. But, if we inspect the formula, we can see that we did not specify TRUE or FALSE for the 4th argument. We simply omitted it.

Here is the key: when omitted, the 4th argument is TRUE.

That means that when we write a VLOOKUP, and don’t specify the 4th argument, it defaults to TRUE. That means, sort order matters! When the data is not sorted in ascending order by the first column, you may get unexpected results.

So, we can resolve the issue by using FALSE (or 0) as the 4th argument, as shown below.

=VLOOKUP(B7,Table1,2,FALSE)

With that update to our formula, it returns Savings, as expected:

So, remember this: when the 4th argument is TRUE or omitted, the lookup range must be sorted in ascending order by the first (lookup) column to return an accurate result. But, when the 4th argument is FALSE (or 0), the data does not need to be sorted.

In the next post, we’ll examine the 4th argument in more detail. It allows us to perform some really fun lookups!

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

10 Comments

  1. SusanM on October 19, 2017 at 12:48 pm

    Thanks for putting a spotlight on this issue! In the past I’ve sorted the table, but ran into the issue when I added some rows & didn’t re-sort. Now I know how to fix the formula, that’s awesome.

    • Jeff Lenning on October 19, 2017 at 12:58 pm

      Welcome 🙂

  2. Patrick Westrick on October 20, 2017 at 10:02 am

    Thanks Jeff, I didn’t know that and it’s always great to learn new things.

    • Jeff Lenning on October 20, 2017 at 10:03 am

      Welcome … I learn new things about Excel all the time 🙂

  3. Mark on October 27, 2017 at 12:28 pm

    Jeff, thanks very much. This repeats something I’ve found in VLookups.
    Using “False” in that 4th step seems to yield a cleaner search and/or
    sort result. It’s helpful to confirm that because it keeps the search simple
    and basic. Mark Ferguson

  4. Dave Slomer on March 23, 2019 at 11:14 am

    Excel 2007 (despite no online help from Microsoft, which is why I’m here) still works well enough for me… or does it…

    When I add a 4th argument to LOOKUP as shown
    =LOOKUP(MID(G25,7,9),F:F,J:J,FALSE)
    I get error: “You have entered too many arguments for this function”
    so I assume this 2017 article only applies to Excel 2010 and beyond.

    Too bad. Great hack in some versions, I guess….

    • Jeff Lenning on March 23, 2019 at 12:50 pm

      It is the VLOOKUP function (not LOOKUP) that is presented in the article, so perhaps adjusting your formula to use VLOOKUP instead may work. Hope it helps…thanks!
      Thanks
      Jeff

  5. Eddie on December 5, 2019 at 12:57 pm

    This hack is so so simple. I have used Vlookup so many times but sorting into order first which can be a pain when new things are added. This works so well THANK YOU.

  6. chathu on April 16, 2020 at 4:34 am

    hi,

    thanks for the video. Do you know how I could auto-sort the VLOOKUP data in realtime? Meaning, as soon as the source data changes, the VLOOKUP data has to change as well showing the greatest value first. Please let me know.

    • Jeff Lenning on April 23, 2020 at 8:36 am

      Depending on your version of Excel, you could use the SORT function and XLOOKUP.
      Thanks
      Jeff

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.