VLOOKUP in Excel: Which formula is the fastest? (2023)

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

Finding information in different spreadsheets is one of the most common tasks in Excel. Unfortunately, the classic VLOOKUP function is notorious when it comes to computational power and flexibility. Not surprisingly, Excel users have developed their own solutions over the years, such as: B. the INDEX MATCH formula. Fortunately, Microsoft finally realized that VLOOKUP has many weaknesses and 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 both methods are 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's the fastest :)

source data

In all examples we use the following data set:

• main tablewith 500,000 rows
• reference bookwith 500 lines

Our goal is to match item names and pull 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 it takes Excel to calculate half a million cells.

Of course, the result depends on many factors such as CPU performance, RAM size, Excel version, etc. Absolute numbers do not matter in this experiment. It's more important to understand how each formula compares, its pros and cons.

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

All testing was done on my Dell laptop with Microsoft 365 Apps for business installed; Excel 32-bit version 2011 build 13415 beta channel. My colleague also ran the same tests in 64-bit Excel and some results are drastically different!

VLOOKUP-Formel

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

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

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

• search value:B2 - the value to look up in the lookup table.
• matrix table: $E$3:$F$501 - Note that when copying the formula, we block the $ references from scrolling.
• col_index_num: 2 - data is restored from 2^{Dakota do Norte}Query table column.
• search area: FALSE: Search for an exact match.

The full formula is:

=BUSCARV(B2, $E$3:$F$501, 2, FALSE)

The formula above goes to C2, and then we double click on the plus sign in the lower right cornercopy the formulaover the entire column. At the moment of the double click we start a timer and see that this calculation takes 6.6 seconds.

When vlookuping real spreadsheets, many people provide the complete columnsmatrix tableto address possible additions in the future:

=BUSCARV(B2, E:F, 2, FALSE)

Does it affect performance? When that happens. 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 line speed.

Your formula not only needs to be fast, but also tough and durable, right? Unfortunately, VLOOKUP cannot boast of reliability and resilience.

Limitations of VLOOKUP

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

• You can't look to the left.The VLOOKUP function can only look up the leftmost column of the table array and return information from the right.
• does not surviveInsert or delete columns. Because 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; whole columns - 14.2 seconds.

in-depth tutoring:Examples of VLOOKUP in Excel for beginners

CORRESPONDENCE RATE formula

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

INDEX (return_column, PHOSPHOR (search value,search column, 0))

For our data set, the formula has the following form:

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

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

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

Irrespective of this, INDEX MATCH pays off with a large number of important advantages.

Advantages of INDEX MATCH

• He canlook 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 range and not a table array.
• Immune to column insertion and removal. You can safely add and remove columns with INDEX MATCH as long as you specify a return range and not an index number.
• The size of a lookup value is unlimited. Although VLOOKUP is limited to 255 characters, INDEX MATCH has no problem handling longer strings.
• CanVlookup with multiple criteriaas shown in the example linked above.
• can make onetwo-dimensional searchand return a value at the intersection of a given row and column.

calculation time: intervals - 8.9 seconds; whole columns - 17.7 seconds; from another sheet - 5.2 seconds.

in-depth tutoring:EQUIVALENT INDEX formula in Excel

COMPENSATION CORRESPONDENCE Formula

Here's yet another vertical search formula in Excel that doesn't have many VLOOKUP limitations:

OFFSET(reference book, PHOSPHOR(search value, OFFSET(reference book, 0,Norte, LINES(reference book), 1) ,0) -1,Metro, 1, 1)

Wo:

• Norte- is the lookup column offset, which specifies how many columns to move from the beginning of the table into the lookup column.
• Metro- It is the offset of the return column that determines how many columns are shifted into the return column.

In our case, the search column offset(s) is 0 because we are searching the first column, so no changes are needed. The return column offset (m) is 1 because the matches are in the second column and we need to shift right 1 column 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! With full columns, OFFSET is slightly slower: 3.5 seconds.

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

In 64-bit Excel, however, the result isn't as impressive: around 7.5 seconds. Why this? A good question for the Microsoft folks :)

Benefits of COMPENSATED DESCENT

In addition to speed, this formula has several other advantages:

• you can make onevlookup from right to leftjTop Search H.
• can make oneBi-directional searchbased on column and row values.
• Does not break when columns are inserted or deleted from the lookup table.

Disadvantages of COMPENSATED GAMING

Complex syntax.

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

detailed tutorials:

• OFFSET function with sample formulas
• How to use the MATCH function in Excel

XLOOKUP-Formel

Microsoft 365 subscribers have a new, more powerful way to find information in their spreadsheets:

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

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

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

Benefits of XLOOKUP

Compared to the traditional VLOOKUP, the XLOOKUP function has many improvements, such as:

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

Disadvantages of XLOOKUP

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?
=BUSCARX(B2, E:E, F:F)

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

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

step by step full:Excel XLOOKUP function with examples

vlookup in Excel Tables

As you probably know, data in Excel spreadsheets can be referenced in special ways, using table and column names instead of cell addresses. This is calledstructured reference, and wonder if that will affect the computation speed.

To verify this, let'sConvert ranges to tablesand try our formulas again.

For convenience, our boards are namedmain table(A1:C500001) zreference 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 a single cell, it is immediately filled in all other cells in the same column. Also, tables are inherently dynamic and will automatically expand to accommodate any new data you enter 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))

=SHIFT(lookup_table, MATCH([@Item], SHIFT(lookup_table, 0, 0, ROWS(lookup_table), 1), 0) -1, 1, 1, 1)

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

Interestingly, Excel spreadsheets are very fast, even withregular references. That is, if you only convert the first range (A1:C500001) to a table and use a VLOOKUP regular formula to extract datasearch area, the entire main table column is calculated in about 2.5 seconds!

computing speed: from 2.3 to 3.3 seconds according to the formula.

End-to-End-Tutorials:

• Excel spreadsheet with examples
• Structured references in Excel spreadsheets

Vlookup with dynamic arrays

The breakthrough change to the Excel 365 calculation engine that took place in January 2020 added support for the so-calleddynamic matrices. In short, they are variable-length arrays that automatically calculate and return values ​​across multiple cells based on a formula entered in a cell.

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

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

It differs from the classic VLOOKUP function in that it returns the entire lookup array for the first argument, not a single lookup value. Since the formula is entered in just 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 PARTY - 4.4 seconds

=ÍINDICE(F2:F501, COINCIDIR(B2:B500001, E2:E501, 0))

XLOOKUP - 7.3 seconds

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

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

computing speed: from 1.8 to 7.3 seconds according to the formula.

in-depth tutoring:Excel rollover, functions and formulas

Extract matches with Power Query

To top it off, let's try another possible solution to our task: Power Query. Of course comparing the calculation of the formula with the update of the query is not entirely correct, but I'm curious which one is faster :)

Detailed steps for using Power Query are described in a separate tutorial below. Here we only evaluate the result:

The merged table loaded from the Power Query Editor into Excel in 8.5 seconds. Unlike formulas, queries are not updated automatically. After each change of the source data, you need to update the resulting table manually by clicking the buttonTo updatebutton on one of theDataÖAdviceEyelash. Our 500,000 rows update 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 you would use when nothing else works.

Performance: load in Excel 8.5 seconds; Refresh 7.6 seconds

in-depth tutoring:How to combine tables with Excel Power Query

Added bonus: table merge wizard

The users of ourUltimative SuiteYou have another tool in your Excel toolbox to join two spreadsheets based on a common column. Let's see how it compares to Excel.

To run the Merge Tables wizard, click the buttonCombine two tablesKnopf anAbandoned Bits DataEyelash. Then just follow the steps in 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 above message, you can see that not all matches were found. However, this does not mean that the tool is defective. 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, while the Table Merge Wizard leaves a cell blank.

Performance: 3.2 seconds

More information:Combine two spreadsheets in Excel

Summary and conclusion

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

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

• Despite all the limitations and disadvantages, VLOOKUP works very well, especially with dynamic arrays.
• INDEX MATCH is not as fast as expected. This looks really weird to me because it renders individual columns, not an array of tables like VLOOKUP.
• XLOOKUP has many amazing features but is slower than SLOOKUP and INDEX MATCH for large data sets. Hopefully Microsoft will improve its performance in future releases.
• OFFSET MATCH is fastest in 32-bit Excel. However, due to the complex syntax, there is a high possibility of making a mistake. Also, it doesn't work with dynamic arrays, at least I couldn't force it.
• There's no point in calculating whole columns unless absolutely necessary. This makes formulas twice as slow.
• Excel spreadsheets are great! To get the most out of your Excel, use them whenever possible.
• Dynamic arrays are the future.

Please note that these observations are based on my testingdynamic excel 365, I did not have the opportunity to test in other versions. If so, your feedback is welcome and greatly appreciated.

Workbook for download

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