In the year 1882, a German philosopher, Friedrich Nietzsche made a claim that has revolutionized 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 using the Order ID?


Is it true that VLOOKUP only looks to the right?


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)


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

  • 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 just like IF function above.


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

Myth 2: 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?


Below is how to do it



Myth 3: VLOOKUP Cannot do Multiple Criteria lookup

Another Widely spread 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!

VLOOKUP can do Multiple criteria Lookup.

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:


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 every time you add or delete the column.

This is not TRUE!

VLOOKUP can handle the Insertion & Deletion of columns.

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

See the below Image.

Vlookup Insertion and deletion of columns

Myth 5: VLOOKUP  is slower than INDEX/MATCH

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

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

After testing the two functions in a worksheet containing over 8000 rows I found that 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” that VLOOKUP goes through in order to do a left lookup.



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 the 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:

Pin It on Pinterest

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.