VLOOKUP IS DEAD

VLOOKUP IS DEAD–OR IS IT?

In the year 1882, a German philosopher, Friedrich Nietzsche made a claim that has revolutionalized Philosophical and Theological discussions since then. His Claim–God is Dead.

There has been a similar discussion in the Excel world, not about God, but about VLOOKUP.

Of all the over 450 functions in Excel, VLOOKUP is one of the most popular and most controversial function.

All excel users can be divided into 2: VLOOKUP die-hards and VLOOKUP bashers. Both sides have evidence to prove their stand.

I have a confession to make, I have been a VLOOKUP basher for a long time until I started to listen to the other side. By softening my hard-stand I have learned a lot that has chattered the many myths I have held about this function.

This is an Article on demystifying the 5 great Myths about VLOOKUP.

  • Myth 1: VLOOKUP Cannot do a Left Lookup
  • Myth 2:  VLOOKUP Cannot Return Multiple columns in a Lookup
  • Myth 3: VLOOKUP Cannot do a Multiple Criteria lookup
  • Myth 4: VLOOKUP Cannot handle the insertion and deletion of columns in the lookup range.
  • Myth 5: VLOOKUP  is slower than INDEX/MATCH

Myth 1: VLOOKUP Cannot do a Left Lookup

Let’s tackle the Elephant in the Room first. This is the widest spread myth about VLOOKUP.

According to this Myth, You can only use Order ID (see Image below) to get either Customer ID or Customer Name as these data are stored in the columns to the right of Order ID.

So what happens when you want to get Product ID, Order Date or Ship Date?

VLOOKUP LEFT

Is it true that VLOOKUP only looks to the right?

NO!

Not only does it look Left but there are 2 ways to make it look left;

  • Using IF Function

Using VLOOKUP and IF function you can get Product ID using Order ID as shown below

=VLOOKUP(H2,IF({1,0},Table1[Order ID],Table1[Product ID]),2,FALSE)

VLOOKUP LEFT COLUMN

How It Works:

The Trick is to create your own Table array using the IF function.

IF({1,0},Table1[Order ID],Table1[Product ID]) creates a range where Product ID appears to the right of OrderID. This Rearrangement makes VLOOKUP get the Product ID

{"CA-2016-152156","FUR-BO-10001798";"CA-2016-138688","FUR-CH-10000454";"US-2015-108966","OFF-LA-10000240";"CA-2014-115812","FUR-TA-10000577";"CA-2017-114412","OFF-ST-10000760";"CA-2016-161389","FUR-FU-10001487";"US-2015-118983","OFF-AR-10002833";"CA-2014-105893","TEC-PH-10002275";"CA-2014-167164","OFF-BI-10003910";"CA-2014-143336","OFF-AP-10002892";"CA-2016-137330","FUR-TA-10001539";"US-2017-156909","TEC-PH-10002033";"CA-2015-106320","OFF-PA-10002365";"CA-2016-121755","OFF-BI-10003656";"US-2015-150630","OFF-AP-10002311";"CA-2017-107727","OFF-BI-10000756"}
  • Using CHOOSE Function

CHOOSE Function can also be used to rearrange your Table array in such a way that Product ID appears to the RIGHT of the Order ID

=VLOOKUP(H3,CHOOSE({1,2},Table1[Order ID],Table1[Product ID]),2,FALSE)

Note: CHOOSE function creates a range where Order ID appears to the LEFT of  Product ID

VLOOKUP

NB: This tricking VLOOKUP to think it is looking to the RIGHT while it is actually looking to the LEFT is possible because VLOOKUP Table_Array can be a Range either from a Syntax eg: A1:F18 or it can be a Named Formula or it can be a formula that returns a Range as a solution.

 

Myth 3: VLOOKUP Cannot Return Multiple columns in a Lookup

It is widely taught that VLOOKUP can only get data from one Column.

Well!, This is not True.

VLOOKUP can return data from as many columns as you want.

For example, given the Product ID, how can you get the OrderDate, Customer ID & Customer Name?

VLOOKUP MULTIPLE COLUMN

Below is how to do it

{=VLOOKUP(G2,A1:E16,{2,4,5},FALSE)}

VLOOKUP

Myth 3: VLOOKUP Cannot do a Multiple Criteria lookup

Another Widespread myth is that VLOOKUP can only have a single ‘lookup_value’. Therefore if your data has duplicates, it will be hard to use VLOOKUP.

Again! This is not TRUE!

All you need is to modify VLOOKUP with an ampersand and IF function.

For example, below we have duplicate sales for a product to different customers. Given the product and customer, ID find the Order ID

VLOOKUP MULTIPLE CRITERIA

Here are the steps:

MULTIPLE CRITERIA

Myth 4: VLOOKUP Cannot handle the insertion & deletion of columns in the lookup range.

I had forgotten this known myth until Shane Devenshire reminded me of it after posting the article on Linkedin.

The myth goes like since you are hard-coding the column index number on the formula, your results will always be tampered with everytime you add or delete the column.

This is not TRUE!

You can have a workaround with MATCH function to provide a dynamic column index number.

See below Image.

insertion & Desertion

Myth 5: VLOOKUP  is slower than INDEX/MATCH

This was another forgotten myth until Shane Devenshire reminded me of it.

There are some truth and a couple of lies in this myth too.

After testing the two functions in a worksheet containing over 8000 rows below is the result.

vlookup vs index match

The truth is VLOOKUP is marginally faster than INDEX/MATCH when doing a right Lookup but fails significantly while doing a left lookup.  This is because of the “panel beating” of VLOOKUP in doing a left lookup.

DOWNLOAD THE SPREADSHEET AND TEST IT

CONCLUSION:

I hope this article has made you loosen your stand if you have always condemned VLOOKUP.

All the same, like all excel function there are things that VLOOKUP cannot do (at least for now until someone finds how)

  • It cannot do a reverse lookup
  • It cannot do a case-sensitive lookup unless using a Helper column
  • In case of Duplicates, It cannot return multiple items

If you know how it can do above three things without a helper column, Please share the knowledge

This Post was inspired by a number of articles. See a few below:

Download Spreadsheet for Excercise

One thought on “VLOOKUP IS DEAD–OR IS IT?”

  1. Vítor Barreto says:

    Great tips!

Leave a Comment:

Your email address will not be published. Required fields are marked *