The VLOOKUP function can only look in the leftmost column of the table array and return information from the right. VLOOKUP limitationsĪs already mentioned, Excel VLOOKUP has a number of irritating constraints. The same result - less than half the speed of the ranges.Īpart from being swift, your formula also needs to be robust and durable, right? Regrettably, VLOOKUP cannot boast of reliability and resilience. That was hard to believe, so I double checked. It takes 14.2 seconds to calculate the entire column. When doing Vlookup in real worksheets, many people supply the entire columns for table_array to cater for possible additions in the future:ĭoes it affect the performance? Yes, it does. At the moment of double-clicking, we run a stopwatch and see that this calculation takes 6.6 seconds.
Perfectly clear 3.5 se plus#
The above formula goes to C2, and then we double click the plus sign in the lower right corner to copy the formula across the entire column. Range_lookup: FALSE - looking up for exact match.Col_index_num: 2 - the data is to be retrieved from the 2 nd column of the lookup table.Table_array: $E$3:$F$501 - please notice that we lock the references with the $ sign so that they don't shift when copying the formula.
Lookup_value: B2 - the value to search for in the lookup table.To pull prices from the lookup table (E2:F501) to the main table (A2:B500001), we define the following arguments for our VLOOKUP formula: VLOOKUP(lookup_value, table_array, col_index_num, ) In fact, it's not that good as it could be, but we'll talk this a bit later :) When it comes to looking up and retrieving matching data in Excel, the first function that comes to mind is the good old VLOOKUP. The same tests were also performed by my colleague on Excel 64-bit, and some results are drastically different! VLOOKUP formula Links to in-depth tutorials are included for your convenience.Īll tests were carried out on my Dell laptop with Microsoft 365 Apps for business installed Excel 32-bit, version 2011, build 13415, Beta Channel. Our examples assume that you have the basic knowledge of the functions, and we won't dwell much on their syntax. It's more important to understand the performance of each formula in comparison, their advantages and drawbacks. In this experiment, it is not absolute numbers that matter. Naturally, the result will depend on many factors such as your CPU performance, RAM size, Excel version, etc. Our goal is to match the items names and pull the prices from the lookup table into the main table.įor each method, we will enter the formula in C2 and copy it down through C500001, measuring the time it takes Excel to calculate half a million cells. In all the examples, we well be using the following data set: But if the number of rows is measured in thousands, the correct choice of the 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 :) If your tables have only a few dozen rows, then most likely any method will be quick enough, and you won't notice the difference.
Luckily, Microsoft has finally realized that VLOOKUP has too many weaknesses and released a more powerful successor - the XLOOKUP function. It's no wonder that over the years Excel users have come up with their own solutions such as an INDEX MATCH formula. Regrettably, the classic VLOOKUP function is notorious when it comes to processing power and flexibility. Today, we are going to do Vlookup in Excel with several different formulas, measure their calculation speed and evaluate consistency, so you can choose your winner.įinding information across different tables is one of the most common tasks in Excel.
0 kommentar(er)
