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 6: VLOOKUP Cannot do a case-sensitive Lookup
- Myth 7: VLOOKUP Cannot do a
**partial**case-sensitive Lookup - Myth 8: VLOOKUP Cannot return the 2nd, 3rd, 4th e.t.c results
- Myth 9: VLOOKUP Cannot Search from Last to First

Table of Contents

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

Alternative to nesting the IF function is using the CHOOSE function

=VLOOKUP(1,

CHOOSE({1,2},((Table147[Product ID]=G2)*(Table147[Customer ID]=H2)),Table147[Order ID]),

2,FALSE)

**How it works:**

CHOOSE function returns a table with 2 columns: Column 1 has 0/1 where 1 represents the row where both criteria are met & Column 2 contains order Ids to return

CHOOSE({1,2},{0;0;0;**1**;0;0;0;0;0;0;0;0;0;0;0},Table147[Order ID])

VLOOKUP checks for the row containing 1 and returns a value from the Orders ID column in a matching row.

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

## Myth 6: VLOOKUP Cannot a Case-sensitive Lookup

I was a firm believer of this myth until it was busted by my encounter with the EXACT function

=VLOOKUP(TRUE,

CHOOSE({1,2},EXACT(F2,Table147[Product]),Table147[Customer ID]),

2,FALSE)

**How it Works:**

CHOOSE function returns a table with 2 columns: Column 1 has TRUE / FALSE depending on where the criterion is EXACT & Column 2 contains Order IDs to return

CHOOSE({1,2},{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},Table147[Customer ID])

VLOOKUP checks for the row containing TRUE and returns a value from the Orders ID column in a matching row

## Myth 7: VLOOKUP Cannot do a **Partial** Case-sensitive Lookup

The trick in understanding this work-around is knowing the difference between the SEARCH & FIND function

How it Works

FIND(F2,Table147[Order ID]) returns a number in rows that contain the partial lookup else returns #VALUE error

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;**5**;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

ISNUMBER function returns TRUE incase it finds a number in the above array else it returns FALSE

CHOOSE({1,2},{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},Table147[Product])

CHOOSE function returns a table with 2 columns: Column 1 has TRUE / FALSE (returned by ISNUMBER function) & Column 2 contains product to return.

VLOOKUP checks for the row containing TRUE and returns a value from the Product column in a matching row

## Myth 8: VLOOKUP Cannot Return the 2nd, 3rd, 4th e.t.c Results

One of my hardest myths to break was that VLOOKUP only returns the 1st result…too bad if your data has duplicates and you need to get the 2nd, 3rd, e.t.c results

Abhijeet Joshi on this Linkedin Article challenged me to look into this…and glad he did.

Here is how to make VLOOKUP return other results

**How it Works:**

(tbl[Product]=$E$2)*(ROW(tbl[Product])-1)… returns row numbers where product is Office Supplies

{1;0;0;0;5;0;0;0;9;0;0;12}

Since we want SMALL function not to return Zero, we use IF function…IF((tbl[Product]=$E$2)*(ROW(tbl[Product])-1)=0,””,(tbl[Product]=$E$2)*(ROW(tbl[Product])-1))

SMALL( {1;””;””;””;5;””;””;””;9;””;””;12}, ROW(A1))

As youdrag your function down, it will return the rows from smallest to largest

CHOOSE function returns a table with 2 columns: Column 1 has row numbers where product is office supplies & Column 2 contains Order ID.

As you drag the formula down VLOOKUP checks for the smallest row number and returns a value from the Order ID column in a matching row

## Myth 9: VLOOKUP Cannot Search from Last to First

This too is one of those myths that has not been busted for a long time

But it is very easy to bust if you understand the above example

All you need to change is the SMALL function with the LARGE function

**DOWNLOAD** VLOOKUP IS DEAD SPREADSHEET

**CONCLUSION:**

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

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

- It cannot do a reverse lookup

If you know how it can do the 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

Great tips!

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

Very well done !!