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

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:

• https://www.extendoffice.com/documents/excel/2453-excel-vlookup-right-to-left.html
• ://chandoo.org/wp/2012/09/06/formula-forensics-no-028/
• www.exceltrick.com/formulas_macros/vlookup-in-excel/
• https://exceljet.net/things-you-should-know-about-vlookup
• https://www.youtube.com/watch?v=yFH01fuVyGs