VLOOKUP in Excel - which formula is faster? (2023)

Today we're going to Vlookup in Excel a bunch of different formulas, measure calculation speed, and evaluate consistency so you can pick the winner.

Finding information in different worksheets is one of the most common tasks in Excel. Unfortunately, the classic VLOOKUP function is notorious when it comes to processing power and flexibility. No wonder Excel users have developed their own solutions like an INDEX-MATCH formula over the years. Fortunately, Microsoft has finally realized that VLOOKUP has many weaknesses and has released a more powerful successor - the XLOOKUP function. So which one is the best?

If your tables only have a few dozen rows, chances are either method is fast enough that you won't notice a difference. But when the number of lines is measured in thousands, the right choice of function is crucial - the difference in performance can be more than 10 times! So let's start the timer and see who is the fastest :)

source data

In all examples, we use the following dataset:

• head tablewith 500,000 lines
• reference bookwith 500 lines

Our goal is to match item names and extract prices from the lookup table into the main table.

For each method, we enter the formula in C2 and copy it to C500001, measuring the time Excel takes to calculate half a million cells.

Of course, the result depends on many factors like CPU performance, RAM size, Excel version, etc. Absolute numbers are not important in this experiment. It is more important to understand how each formula performs in comparison, its advantages and disadvantages.

Our examples assume you have a basic understanding of functions, and we won't go into too much detail about their syntax. Links to detailed tutorials are included for your convenience.

All tests were done on my Dell laptop with Microsoft 365 Apps for Business installed; Excel 32-bit, version 2011, build 13415, beta channel. The same tests were also run by my colleague on 64-bit Excel and some results are drastically different!

VLOOKUP formula

When it comes to searching and retrieving appropriate data in Excel, the first thing that comes to mind is the good old VLOOKUP function. It's actually not as good as it could be, but we'll discuss that a little later :)

SVERWEIS(Lookup_value, table_array, col_index_num, [range_lookup])

To extract prices from the lookup table (E2:F501) to the main table (A2:B500001), we define the following arguments for our VLOOKUP formula:

• Lookup-Wert:B2 - the value to be looked up in the lookup table.
• Table_array: $E$3:$F$501 - Note that we lock the references with the $ sign so they don't move when the formula is copied.
• column index_num: 2 - data must be retrieved from 2^ndLookup table column.
• reach search:FALSE - Look for an exact match.

The complete formula is:

=PROCV(B2, $E$3:$F$501, 2, FALSO)

The above formula goes to C2, and then we double-click the plus sign in the bottom right cornercopy the formulaalong the entire column. At the moment of the double click, we run a stopwatch and see that this calculation takes 6.6 seconds.

When Vlookup on real worksheets, many people return the entire columns toTabellenarrayto consider possible additions in the future:

=PROCV(B2, E:F, 2, FALSO)

Does it affect performance? Yup. It takes 14.2 seconds to calculate the entire column. That was hard to believe so I double checked. Same result - less than half the speed of scopes.

Your formula doesn't just need to be fast, it also needs to be tough and durable, right? Unfortunately, VLOOKUP cannot boast of reliability and resilience.

VLOOKUP restrictions

As mentioned, Excel VLOOKUP has several annoying limitations. The most important ones are:

• I can't look left.The VLOOKUP function can only look in the left column of the table array and return information from the right.
• don't survive itInsert or delete columns🇧🇷 Since the return column is specified as an index number, a VLOOKUP formula will stop working as soon as a new column is added or removed from the table array.
• A search value is limited to 255 characters.

calculation time: range - 6.6 seconds; entire columns - 14.2 seconds.

detailed tutorial:Excel VLOOKUP Examples for Beginners

INDEX MATCH Formula

For many users, the INDEX MATCH magic formula is an advanced form of Excel lookup. In generic form it looks like this:

INDEX (return_column, TO ADEQUATE (Lookup-Wert,search_column, 0))

For our dataset, the formula has the following format:

=INDEX($F$2:$F$501, MATCH(B2, $E$2:$E$501, 0))

As with VLOOKUP, remember to useabsolute referencesto search and return ranges to ensure the formula is copied correctly to the cells below.

As both tables are on the same worksheet, INDEX MATCH was much slower than VLOOKUP (8.9 vs. 6.6 seconds).

But if we move the lookup table toanother worksheet, the formula starts working much faster (~5 seconds), which is better than VLOOKUP.

In any case, INDEX MATCH compensates for time with a number of important advantages.

Advantages of INDEX MATCH

• allowed forlook from right to left🇧🇷 Yes, an INDEX MATCH formula doesn't care where the lookup column is because, unlike VLOOKUP, it explicitly defines a lookup scope and not a table array.
• Immune to inserting and deleting columns. With INDEX MATCH you can safely add and remove columns because you are specifying a return range and not an index number.
• There is no limit to the size of a lookup value. While VLOOKUP is limited to 255 characters, INDEX MATCH has no problem handling longer strings.
• can performVlookup with multiple criteriaas shown in the example linked above.
• can atwo-dimensional searchand return a value at the intersection of a given row and column.

calculation time: enough - 8.9 seconds; entire columns - 17.7 seconds; from another sheet - 5.2 seconds.

detailed tutorial:INDEX MATCH-Formel no Excel

COMPENSATION CORRESPONDENCE Formula

Here is another vertical lookup formula in Excel free from many VLOOKUP limitations:

OFFSET(reference book, TO ADEQUATE(Lookup-Wert, OFFSET(reference book, 0,n, ROW(reference book), 1) ,0) -1,m, 1, 1)

Wo:

• n- is the lookup column offset, which specifies how many columns should be moved from the beginning of the table to the lookup column.
• m- is the offset of the return column, which determines how many columns are shifted to the return column.

In our case the search column offset(n) is 0 as we are looking in the first column so no offset is needed. The return column offset (m) is 1 because the matches are in the second column and we need to shift 1 column to the right to get to them:

=OFFSET($E$2:$F$501, MATCH(B2, OFFSET($E$2:$F$501, 0, 0, LINES($E$2:$F$501), 1), 0) -1, 1, 1, 1)

Compared to the previous solutions, the formula is too complicated, isn't it? However, it is much faster than VLOOKUP or INDEX MATCH. Calculating 500,000 rows takes less than 3 seconds! For full columns, OFFSET is only slightly slower - 3.5 seconds.

=OFFSET(E:F, MATCH(B2, OFFSET(E:F, 0, 0, LINES(E:F), 1), 0) -1, 1, 1, 1)

However, in 64-bit Excel, the result is not so impressive - about 7.5 seconds. Why is this? A good question for Microsoft folks :)

Advantages of OFFSET MATCH

In addition to speed, this formula has some other merits:

• can perform aVlookup from right to leftelittle hlookup.
• can abidirectional searchbased on column and row values.
• Does not break when columns are inserted or deleted from the lookup table.

OFFSET MATCH Disadvantage

Complex syntax.

calculation time: intervals - 2.9 seconds; entire columns - 3.5 seconds.

detailed tutorials:

• OFFSET function with example formulas
• How to Use the MATCH Function in Excel

XLOOKUP formula

Microsoft 365 subscribers get a new, more powerful way to look up information in their spreadsheets:

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

For our purpose, the default values ​​of the last 3 arguments work fine, so we only provide the first 3 necessary parameters. Argument names are intuitive and I think you can understand the formula without further explanation:

=XVERWEIS(B2, $E$2:$E$501, $F$2:$F$501)

XLOOKUP benefits

Compared with the traditional VLOOKUP, the XLOOKUP function has many improvements, including:

• Simplified and more meaningful syntax
• Ability to look up vertically and horizontally in any direction: right, left, down or up.
• For sorted data it has a special binary search mode which is much faster than normal search.
• Search inreverse orderto get the latest occurrence.
• ability to returnmultiple values.
• Vlookup with multiple criteria.
• built inif errorfunctionality.

XLOOKUP cons

XLOOKUP is only available in Excel 365. It is not supported in Excel 2019, Excel 2016 and earlier versions.

Now let's see how fast this new feature is. 11.2 seconds - quite disappointing :(

What if we use column references instead of ranges?
=XVERWEIS(B2, E:E, F:F)

24.5 seconds. Speechless... Almost twice as slow as VLOOKUP.

calculation time: intervals - 11.2 seconds; entire columns - 24.1 seconds.

comprehensive tutorial:Excel XLOOKUP function with examples

Vlookup in Excel tables

As you probably know, data in Excel spreadsheets can be referenced in a special way - using table and column names instead of cell addresses. This is referred to as astructured reference, and I wonder if it affects the calculation speed.

To check this, let'sConvert ranges to tablesand test our formulas again.

Our tables are named for conveniencehead table(A1:C500001) ereference book(E1:F5001).

To create a table reference, start typing the formula in the first cell (C2), select the cells and ranges you want to reference, and Excel will automatically insert the structured references.

For example, the VLOOKUP formula looks like this:

=VLOOKUP([@Item], Lookup_table, 2, FALSE)

A great feature of Excel spreadsheets is that as soon as you enter a formula in just one cell, it is immediately populated in all other cells in the same column. Also, tables are inherently dynamic and will automatically expand to accommodate any new data entered next to a table.

In our spreadsheet, the VLOOKUP formula is calculated in 2.3 seconds, INDEX MATCH in 2.6 seconds, OFFSET and MATCH in 2.7 seconds, and XLOOKUP in 3.3 seconds. As you can see, the calculation speed increases significantly compared to intervals.

The formulas are listed below for your reference:

=INDEX(Lookup_table[Preis], MATCH([@Item], Lookup_table[Item], 0))

=OFFSET(Lookup_table, MATCH([@Item]), OFFSET(Lookup_table, 0, 0, ROWS(Lookup_table), 1), 0) -1, 1, 1, 1)

=XLOOKUP([@Item], LookupTable[Item], LookupTable[Preço])

Interestingly, Excel spreadsheets are also very fast withregular references🇧🇷 That is, if you convert just the first range (A1:C500001) to a table and use a normal VLOOKUP formula to extract data fromSearch area, the entire column in the main table is calculated in about 2.5 seconds!

computing speed: from 2.3 to 3.3 seconds depending on the formula.

End to end tutorials:

• Excel spreadsheet with examples
• Structured References in Excel Sheets

Vlookup with dynamic arrays

The revolutionary change in the calculation engine of Excel 365, which occurred in January 2020, has support for the so-calleddynamic arrays🇧🇷 In short, they are resizable arrays that are automatically calculated and return values ​​in multiple cells based on a formula entered into a cell.

One of the best things about dynamic arrays is that they can be used with just about any traditional Excel function. For our VLOOKUP formula, it looks like this:

=SVERWEIS(B2:B500001, E2:F500001, 2, FALSCH)

The difference from the classic VLOOKUP function is that you supply the entire lookup array for the first argument, not a single lookup value. Since the formula is only entered into one cell, you don't have to worry about blocking ranges with absolute references.

In terms of performance, dynamic arrays work even faster than Excel spreadsheets! Half a million cells are filled with results almost instantly: 1.8 seconds - very impressive!

Other results are listed below:

INDEX COMPARISON - 4.4 seconds

=ÍNDICE(F2:F501, ÜBEREINSTIMMUNG(B2:B500001, E2:E501, 0))

XLOOKUP - 7.3 seconds

=XVERWEIS(B2:B500001, E2:E501, F2:F501)

Hmm... XLOOKUP, which is supposed to be dynamic by design, is worse than the older features. Weird!

computing speed: from 1.8 to 7.3 seconds depending on the formula.

detailed tutorial:Excel dynamic arrays, functions and formulas

Get matches with Power Query

For the sake of completeness, let's test one more possible solution to our task - Power Query. Of course it's not entirely correct to compare calculation formulas with query update, but I'm curious which one is faster :)

Detailed steps to use Power Query are described in a separate tutorial mentioned below. Here we only evaluate the result:

The merged table loaded from Power Query Editor into Excel in 8.5 seconds. Unlike formulas, queries are not automatically updated. After each source data change, you must update the resulting table manually by clicking onUpdatebutton on any of theDataorinvestigationTab. Our 500,000 rows will be updated in about 7 seconds. Not bad, but Excel formulas could be better. Considering that setting up a query is anything but a one-click process, this is probably the last method I would use, just when nothing else works.

performance: load in Excel 8.5 seconds; Refresh 7.6 seconds

detailed tutorial:How to Combine Tables with Excel Power Query

Extra Bonus: Table Merge Wizard

The users of ourUltimate Suitehas one more tool in your Excel toolbox to join two worksheets based on a common column. Let's see how it compares to Excel.

To run the Merge Tables wizard, clickmerge two tablesbutton onAbandoned bit dataTab. Then, just follow the steps of the wizard that will guide you through the process.

Well, the tool took about 3 seconds to complete. Not bad for half a million records!

If you take a closer look at the message above, you can see that not all matches were found. However, that doesn't mean the tool is buggy. It just lets you know that some items (lookup values) are not present in the lookup table. The VLOOKUP function returns a #N/A error in this case, whereas the Table Merge Wizard leaves a cell blank.

performance: 3.2 seconds

More information:Merge two worksheets in Excel

summary and conclusion

If you've carefully read all the examples, you've probably already drawn your own conclusions. If you skipped over the details, check out this comparison chart for a quick recap:

Below are some observations I made based on the test results. Perhaps they will be useful for you too.

• Despite all its limitations and disadvantages, VLOOKUP works very well, especially for dynamic arrays.
• INDEX MATCH is not as fast as expected. This looks really weird to me because it deals with single columns and not a table array like VLOOKUP.
• XLOOKUP has many amazing features, but it is slower than SLOOKUP and INDEX MATCH on large datasets. We expect Microsoft to improve its performance in future versions.
• OFFSET MATCH is the fastest in 32-bit Excel. However, due to the complex syntax, there is a high chance of making a mistake. Also, it doesn't work with dynamic arrays, at least I couldn't force it.
• It makes no sense to calculate entire columns unless absolutely necessary. This makes formulas twice as slow.
• Excel spreadsheets rock! To get the most out of your Excel, use them whenever possible.
• Dynamic arrays are the future.

Keep in mind that these observations are based on my testing inDynamic Excel 365, I did not have the opportunity to test in other versions. If so, your comments are welcome and greatly appreciated!

downloadable workbook

Fastest VLOOKUP formula in Excel(.xlsx file,74 MB)