Tag Archives: vlookup

Excel XLOOKUP vs VLOOKUP – Which is better and why?

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

The XLOOKUP function has been just recently announced as a new addition to the Excel functions base. With it came also other less known functions such as the XMATCH etc. This seems to end a decades old battle between fans of VLOOKUP vs INDEX MATCH. Even news sites caught this announcement as somewhat special. In this post we will understand the differences between both – at least based on the limited information we know.

To understand the differences of XLOOKUP vs VLOOKUP it is best we compare the definitions of both formulas and explain using examples.

VLOOKUP Basics

Starting with a reminder of the VLOOKUP function. The Excel VLOOKUP function allows you to find a row in a column that matches a certain value and returns a value from another corresponding column in that row. It works like a phone book e.g. find the name of a person and return his/her phone number.

VLOOKUP Definition and Example
The problem with the VLOOKUP was that:

  • The lookup_value needed to be in the first column of the table_array. Otherwise you would need to reorganize the columns
  • The col_index_num was not a reference on the number of the column in the table_array where the returned value can be found. Hence adding a column in between would cause the whole formula to return values from the incorrect column
  • The range_lookup was most of the time a useless argument, usually being set to FALSE (exact match)

XLOOKUP Basics

The XLOOKUP does the same operation as a VLOOKUP, however, is much more flexible even in its most basic version (and familiar to an INDEX MATCH combo).
XLOOKUP Definition and Example
To summarize the benefits:

  • No longer do you need to worry about the lookup column being first in your table
  • You don’t have to worry about breaking any lookup formulas by adding or deleting columns from your table
  • You save time – as the basic version of XLOOKUP assumes you are looking for exact matches

Advanced XLOOKUP

We can also explore the full definition for the XLOOKUP function:

We already described the first 3 parameters however we also have 2 remaining:

  • match_mode – the type of match you want to make. What is interesting is the mode 2 allowing you to use ? and * wildcards to replace a single or any number of characters in a lookup operation xlookup match mode
  • search_mode – type and direction of search. Useful when there is a particular sorting order of data.xlookup search mode

I can’t wait to try this function out on actual data.

XLOOKUP vs VLOOKUP – Conclusions

When will XLOOKUP become available to Excel users? As per this article:

XLOOKUP is currently a beta feature, and only available to a portion of Office Insiders at this time. We’ll continue to optimize it over the next several months. When XLOOKUP is ready, we’ll release it to all Office Insiders, and Office 365 subscribers.

Although XLOOKUP is still only recently announced I already see it as a final resolution to the problem of lookup operations in Excel. I am frankly much surprised it took Microsoft so long to introduce this function. If you are interested in how XLOOKUP will compare in performance to VLOOKUP stay tuned.

Get VLOOKUP Multiple Matches – Multi INDEX MATCH in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

The Excel VLOOKUP function by default allows you to find only a single match and will return the corresponding row of a selected column value. What if you want to find VLOOKUP multiple matches, not just the first one? In this post let us explore this more complicated scenario. Instead of VLOOKUP however we will use INDEX and MATCH.

Using the MATCH Function in Excel

To find the first MATCH of the “A” value in column B:B we use the following formula as shown on the image below:

See the scenario below:
Get single Excel MATCH

Finding multiple matches in Excel

Now say we want to find all matches of “A” in column B:B as seen below.
Get VLOOKUP Multiple Matches in Excel
Below the formulas in cells E2-E4. In E2 we find the corresponding row of the first “A”, then in subsequenty (E3-E4) we look for the rows of the next found “A”. You can drag this formula down as much times as needed.

VLOOKUP Multiple Matches

To do a multiple match VLOOKUP we simply need to expand on the above Multiple MATCH example and add the INDEX function like so:

Using VBA to do a VLOOKUP Multi Match

In case you want a more sophisticated approach to doing a multi match INDEX MATCH / VLOOKUP you can also use the VBA Dictionary to record all instances of all lookup values along with selected columns. A simple version of this approach can be found in my post about using VLOOKUP in VBA. Below, however, I expanded this example by using a VBA Collection inside the VBA Dictionary to store value associated with each match of every lookup value (basically creating a very simply tree-like structure).

Based on the “A1:B10” table above the VBA code below will create my dictionary dict object.

After creating the dictionary I can now print all values from column “A:A” for any value of column “B:B”:

VBA VLOOKUP – Using VLOOKUP in VBA

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.00 out of 5)
Loading...

The Excel VLOOKUP function allows you to lookup a value in a table based on a corresponding lookup value in the first column of a table. Below we will explore how to use VLOOKUP in VBA using tWorksheetFunctions as well as how to use the VBA Dictionary as an alternate approach.

VLOOKUP is easily used in Excel. Today, however, we will explore how to do lookup operations in VBA (VBA VLOOKUP) or how to VLOOKUP in VBA if you prefer.

Excel VLOOKUP

We will start with looking at the Excel VLOOKUP function. For this example table:
vba vlookup table
A simple VLOOKUP operation for say to lookup Roberts age might look like this:
VLOOKUP in Visual Basic equivalent

VBA VLOOKUP WorksheetFunction

Now we will explore using the VBA VLOOKUP WorksheetFunction. The below is the equivalent to the VLOOKUP operation in Excel. We can use the WorksheetFunction object to run a VLOOKUP operation as such:

WorksheetFunction VLOOKUP function

The syntax for the Vlookup function in VBA is:

VLOOKUP Parameters

Parameter Description
Lookup_value The lookup value – the value which is searched for within the first column of the provided Table_array parameter
Table_array A VBA Range variable. A table of at least 2 columns or more. The first column is the lookup column
Col_index_num The number of the column in the Table_array table VBA Range from which the matching value must be returned
Range_lookup An optional parameter. A logical parameter (True or False) that specifies whether you want the VLookup method to find an exact match or an approximate match. True – approximate match, False – exact match.

VBA VLOOKUP Dictionary

Another approach is to use the VBA Dictionary Object. For the same table as above we can use the following code:

What happens above? First we loop through all the rows and create a VBA Dictionary object with a Key-Value pair of all rows. The Dictionary contains only the lookup and matching columns. Next we lookup our desired Key and return the matching Value.

Benefits of using the VBA Dictionary

Right. So if both ways provide the same result what is the benefit of building a Dictionary upfront? Well, imagine wanting to lookup multiple values within your VBA code. In the first approach you would have to reintroduce the entire arguments of the Vlookup function. In the second you can run additional simple calls:

The Dictionary approach will in this case prove MUCH FASTER. This is because the Dictionary is built once and then just reused to lookup a certain key as opposed to doing the entire lookup operation on the entire table as a Vlookup operation would do.

If you want to learn more on the most effective way to lookup values in Excel instead, read my article on VLOOKUP vs INDEX MATCH vs SQL vs VBA.

How to use VLOOKUP in Excel!

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

The Excel VLOOKUP function (Vertical Lookup) is one of need-to-know features in Excel! I can’t stress enough how many articles/posts out there have been published on using VLOOKUP. Probably rightly so. I couldn’t however resist to make an my attempt at consolidating all the knowledge around how to use VLOOKUP and all the bits and pieces of information you need to know to be able to fully leverage the vertical lookup function in Excel! Let’s start with a VLOOKUP example…

The VLOOKUP Phonebook analogy

To quickly explain what VLOOKUP does I usually like to use the common Phonebook analogy. In the example below imagine we have a friend called John White. Let’s say we want to call John. What we need to do is to locate John in our Phonebook by his Name and Surname and call him on his corresponding phone number located within the same row.

The VLOOKUP Phonebook analogy
The VLOOKUP Phonebook analogy

VLOOKUP returns a cell located at a row corresponding to your lookup value. In a phonebook analogy you would be looking-up a person’s phonenumber by his name and surname

Everyday we deal with similar lookup problems e.g. restaurant menu (looking up the dish price), grocery lists (how many bottles of milk did I need to buy?) etc. Hence the usefulness of using VLOOKUP. Let us move on to learn how to use VLOOKUP.

How to use VLOOKUP?

Considering the above VLOOKUP example let’s take a closer look at the parameters of the VLOOKUP function:

Excel VLOOKUP Function Parameters
Excel VLOOKUP Function Parameters

Let’s look at the arguments of VLOOKUP in more detail:

Parameter Description
lookup_value value you want to look up. Must be in the first column of the range of cells you specify in table-array
table_array range of cells VLOOKUP will

  1. Search FIRST column to find the ROW containing the lookup_value
  2. Search and return corresponding value in column col_index_num of the table_array
col_index_num column number within the table_array that contains return values. The first left-most column in the table_array starts numbering with 1
range_lookup an OPTIONAL boolean (True/False) parameter that defines how the VLOOKUP function will behave:

  1. FALSE – search for the first EXACT MATCH in the first column of the table_array
  2. TRUE – Default option. assume the FIRST COLUMN in table_array is sorted either numerically or alphabetically and return an APPROXIMATE MATCH. This parameter works correctly ONLY IF THE FIRST COLUMN OF THE table_array IS SORTED!

FALSE VLOOKUP Example

Nothing beats a hands-on VLOOKUP example! See the below animation to witness for yourself how VLOOKUP works!

VLOOKUP Example
VLOOKUP Example

Notice that I am using the range_lookup set to FALSE. This means that the VLOOKUP function will always look for an EXACT MATCH.

TRUE VLOOKUP Example

In the previous example the range_lookup parameter was set to FALSE. This example will equally work with range_lookup equal to TRUE as long as we sort our FIRST COLUMN alphabetically (or numerically in other cases)!

TRUE VLOOKUP
TRUE VLOOKUP

So what’s the difference you will ask? PERFORMANCE. The TRUE (approximated match) VLOOKUP is significantly faster than the FALSE (exact match) VLOOKUP. Read here more on VLOOKUP Performance.

Important! If you got hooked up already on the performance bit of the TRUE VLOOKUP beware! The TRUE VLOOKUP will always return a result… even if it didn’t find an exact match. That is a setback. Luckily Excel experts have found a way around this with a DOUBLE TRUE VLOOKUP. Read on.

DOUBLE TRUE VLOOKUP

To exemplify the DOUBLE TRUE VLOOKUP let’s consider this table:
table
We want to match the Animal Monkey against a Category. If we use a standard FALSE VLOOKUP like this:

=VLOOKUP("Monkey";A1:B5;2;FALSE)

We will get the following results:

"Mammal" if found
"#N/A" if not found

Great! But what happens if we use a simple TRUE VLOOKUP?:

=VLOOKUP("Monkey";A1:B5;2;TRUE)

We will get the following results:

"Mammal" if found
"Amphibian" (or similar) if not found

Well that’s not very appropriate huh? How can we know for sure if the TRUE VLOOKUP cross-referenced the Animal correctly? Well.. why not use a second VLOOKUP for that? Consider the DOUBLE VLOOKUP below:

=IF(VLOOKUP("Monkey";A1:B5;1;TRUE)="Monkey";VLOOKUP("Monkey";A1:B5;2;TRUE);)

We will get the following results:

"Mammal" if found
"" if not found (replace "" with #N/A if needed)

See what happens here? The first VLOOKUP validates whether there is an EXACT MATCH in the first column. If so the second VLOOKUP will return the corresponding result. See below:

DOUBLE TRUE VLOOKUP Example
DOUBLE TRUE VLOOKUP Example

Are 2 DOUBLE TRUE VLOOKUPs really worth the trouble? If you are looking for performance than YES! Read more here.

INDEX MATCH vs VLOOKUP – Do’s and Don’t

If you’ve been using VLOOKUP long enough you probably stumbled across MANY articles/posts on why many consider a certain combination of two Excel functions i.e. INDEX MATCH, better than using a regular VLOOKUP. In fact MOST Google results for VLOOKUP vs INDEX MATCH won’t mention the significant advantage VLOOKUP has over INDEX MATCH which I hinted in the previous section.

Let’s compare the pro’s and con’s of using INDEX MATCH (instead of VLOOKUP):

  • More flexible – allows you to match both against rows and columns
  • Less error prone – adding/removing columns/rows from the lookup table should not crash the INDEX-MATCH combo
  • Both vertical and horizontal lookups – VLOOKUP and HLOOKUP address either only vertial or horizontal lookups, whereas with the INDEX MATCH you can easily do both
  • Harder to use – VLOOKUP is a little easier to understand than INDEX-MATCH and I know some people have difficulty with this two step approach
  • Slower (at least in newer versions of Excel like 2013) – opposed to the broadly shared myth INDEX MATCH is no longer faster than a simple VLOOKUP. Although, I still think INDEX-MATCH is better in most cases. Read my post here for how VLOOKUP compares in terms of performance against INDEX MATCH.

VLOOKUP with Multiple criteria

In some cases you would like to run a VLOOKUP against MORE THAN ONE COLUMN i.e. lookup a certain set of values against several columns instead of just one column. There is a simple way to achieve this by introducing a HELPER COLUMN with a concatenation of the lookup columns. Let’s consider the example below:

VLOOKUP Multiple Criteria: Example
VLOOKUP Multiple Criteria: Example

As you can see each month is specified in two separate columns. A simple one-column VLOOKUP will not do. So how to use VLOOKUP to get the result for multiple criteria? Using a HELPER COLUMN! See the solution below:
VLOOKUP Multiple Criteria: Solution
VLOOKUP Multiple Criteria: Solution

The above is the simplest approach to a multiple criteria VLOOKUP. There are more elegant approaches that need not a Helper Column e.g. check-out Chandoo’s example here.

VLOOKUP Performance

If you are impatient for the answer – for best performance always use DOUBLE TRUE VLOOKUPS. Now for a more thorough explanation let’s start from the beginning and summarize what we know in bullet points:

  • The VLOOKUP function can be well replaced with other functions/features in Excel
  • A common practice it to replace a VLOOKUP with a INDEX MATCH function combo. This does not affect performance (much) but solve a lot of typical usability issues
  • I have hinted above that a TRUE (approximated) VLOOKUP on a sorted table_array will have better performance that a regular FALSE (exact match) VLOOKUP

The TRUE (approximate) VLOOKUP function seems to be best candidate in terms of performance. However, approximating the result may render certain issues – a TRUE VLOOKUP will ALWAYS return a result! Even if there is no exact match! This creates a certain issue for us. Luckily there is a certain trick to use 2 TRUE VLOOKUPs in a combo to replace a regular FALSE VLOOKUP and expect similar results. This is commonly called a DOUBLE TRUE VLOOKUP.

Performance: VLOOKUPs VS INDEX MATCH VS SQL

The below performance comparison was carried out by me in my separate post on VLOOKUP vs INDEX-MATCH vs SQL. Follow to my post to read more. In the meantime let’s look at the comparison:

VLOOKUP Performance Comparison
VLOOKUP Performance Comparison

What do the various categories mean?:

  • VLOOKUP (sorted) – a regular FALSE (exact match) VLOOKUP against a sorted table_array
  • DOUBLE TRUE VLOOKUP (sorted) – 2 combined TRUE (approximate match) VLOOKUPs against a sorted table_array
  • INDEX-MATCH (sorted) – a combination of the INDEX and MATCH functions against a sorted table_array
  • SQL (Sorted) – an Excel MS Query (SQL) executed against a sorted table_array

How to use VLOOKUP recap

Well I do hope this exhausts the subject of using VLOOKUP in Excel. VLOOKUP is a common used function in Excel sometimes wrongly which, I hope from my performance stats above, can seriously tamper with your Workbook performance causing Excel Workbooks to recalculate in matters of minutes instead of seconds.

Optimize Excel formulas with the Excel Optimizer!

1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 4.33 out of 5)
Loading...

Ever been in a situation where your Excel workbook runs so slow you could make yourself a coffee before it finishes recalculating? Fighting to reduce the file size of your latest Excel report? Or just looking to optimize your Excel workbook a bit? Welcome to the AnalystCave Excel Optimizer the AddIn that will optimize Excel for you! Excel Optimizer
Stay tuned for updates on this post!
I must say this idea has been popping in and out of my head for some time now – although it turned out the first working version of the Optimizer took me only a couple of hours to build. Often coming along Excel files that literally called for wrath from the Gods, I yearned for a time that Excel would include a “Fix Me” button – doing away with all the wrongs of the Workbook I was working on. Unfortunately this is (still) not the case and building your Excel file many of us have to be constantly aware of the dangers that lie ahead. What is more, many Excel users waste a lot of time not appreciating simple tips and tricks that can seriously enhance their Excel experience (like simply saving files in XLSB file format).

Optimize Excel with the Excel Optimizer

The Excel Optimizer is a simple AddIn to Excel that runs certain rules across your Workbook and points out the main pain-points or suggests certain improvements – all with performance in mind. Some rules can be implemented (I am hoping making all as such) while others will simply point out what needs to get done.

This is still a beta-work-in-progress-prototype so keep in mind that there may be some error/bugs here and there. What is more the Excel Optimizer current will only be able to suggest certain fixes (such as replacing some Excel functions with others). Depending on how popular this AddIn becomes I am planning to extend it to be able to actually implement most of the recommended tweaks. Optimizing Excel for best performance has become much easier.

See this video for a quick showcase of how the Excel Optimizer works.

Documentation

The Excel Optimizer consists of 2 features:

  • Optimize Excel – the main feature. Opens a window (screen below) that will allow you to run a rule-analysis on your current Workbook. Each rule with validate against different performance standards, rules also have different scopes. There are rules validating the format of the Workbook, validating worksheets and then there are the low-level rules that validate each and every formula. Using the settings button you can turn rules on/off or configure them to enhance your experience
  • Timer Full Calculation Rebuild – a simple macro that calculates the time needed to do a Full Rebuild Calculation of your Excel workbook. This comes in handy when wanting to set a baseline for the improvements you might want to implement

Excel Optimizer: AddInRibbon buttons
Excel Optimizer: AddInRibbon buttons

Below a screenshot of how the main window looks like:
Excel Optimizer: Main window
Excel Optimizer: Main window

Rules

Rules are what drive the Excel Optimizer. The AddIn has been built in a way to make adding/removing/enabling/disabling rules as easy as possible. Currently the Excel Optimizer introduces the following types of rules – many of most still cannot be implemented automatically (expect this in the near future):

Rule Scope Description
XLSB Workbook Checks if workbooks is in XLSB format if not suggests so
UsedRange Worksheet Validates the Used Range of each Worksheet to see if the Used Range is excessive and can be reduced for better performance and optimum file size
VLOOKUP Range Looks for exact (FALSE) VLOOKUPs. If found suggest replacing them with double approximate (TRUE) VLOOKUPs for best performance
Repeating formula Range Looks for exact repetitions of certain functions and suggest replacing them with a certain cell to minimize the need to recalculate the same function multiple times
Volatile functions Range Looks for volatile native Excel functions and suggests removing or replacing them
External links Range Looks for any external links used within cells in the entire Workbook and suggests removing them or reducing to a bare minimum
Array Formula Range Looks for Array Formulas within cells in the entire Workbook and suggests replacing them with regular Excel functions if possible
Error Range Looks errors within cells in the entire Workbook and suggests correcting them

Download

You can download the latest version here:

Do you have ideas for new rules? Any suggestions / wishes for additional features? Put your comment below or on my Facebook / Twitter page.