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?**

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)`

**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 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

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

# 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

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.

# 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.

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

Great tips!

Thanks Victor

Very interesting, and the solution to kick the myth 4 is for me extremly useful.

thanks for sharing!

You are welcome Kilchoer!

Let’s keep busting these myths

Very well done and really appreciate the openness to listening to counter-arguments and thinking outside the box.

This is not a knock on you and maybe not the best place for this; just a general comment: As an analytical guy but infrequent user of Excel, and definitely not an expert, I appreciate simplicity and intuitive constructs. It seems the workarounds are a bit cumbersome and even the Index Match idea may be difficult (or time consuming or prone to technical and grammatical errors). Most Excel functions are pretty straight forward and logical but it’s obvious by the many suggestions and solutions that the lookup functions need work. I’ve seen some great suggestions but it’s been awhile and can’t remember which ones I would recommend. However, in my humble opinion to make things easier and more intuitive for the bigger population:

1) There should only need to be one lookup function with “options” that do the extra things people want, and formatted the same as other functions. Otherwise people are spending/wasting their time just trying to figure out which function to use. Notice that if you use Help, it almost always says at the bottom, see also … I realize for backward combatability some old functions might need to remain, but it;s a simple matter of labeling them as such; e.g., VLOOKUP (old function still functional but primarily used in older spreadsheets)

2) The order and syntax used in Excel functions should be more consistent.

For example, the order of arguments should generally be the same in all Excel functions. Half the time it seems you start the function with the field/data you are referencing and for other functions you start with the result location. This is simple and easy to fix if you know Excel inside and out, but frustrating and time-consuming and much more error prone if you don’t.

Also, some times you put things in quotes and sometimes you don’t and usually there is no warning or clue as to why the function doesn’t work properly. Same goes for punctuation like commas and colons.

3) The HELP in Excel has gotten better, mentioning the limitations of functions. Some more work on that is needed.

Of course none of this matters if the object is to make Excel like your taxes, a lot of time or professional help needed. Unfortunately, I ‘m guessing it hurts Excel professionals as well while they waste time trying to get these functions to work properly.

Thanks, CL for the comment.

True, as good as Excel is there is a bit of a gap in its Lookup functions.

Looking forward to what Microsoft team will come up with next.

BTW, try the AGGREGATE function, it is a much stronger lookup function in my opinion.

Thanks for sharing!!!

You are welcome Javier

Ahoy Crispo,Good work.Tell us more about he AGGREGATE function

Ahoy Crispo,Good work.Tell us more about he AGGREGATE function

Hi David,

Thanks for the comment.

Definitely I will be writing on AGGREGATE Function

Very well done !!

Thanks Tahsin