Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Formula errors in Excel when list separator is not set correctly
In this articleSymptomsWhen entering a formula an error will occur if trying to use a symbol that is not the default 'list separator' in the Windows Regional settings.
CauseIf the error occurs when you use a character to separate the arguments you expect to work and Excel will not accept it, then this is typically caused by either or both of the following scenarios:
ResolutionTo work around the error message follow the following steps:
Note By default, Microsoft Excel uses the system separators that are defined in the regional settings in Control Panel. If you sometimes need to display numbers with different separators for thousands or decimals, you can temporarily replace the system separators with custom separators. To do that please follow the steps in the following article. The comma is the default list separator for US - English Locale. Changing the List separator in the Windows Region settings will affect the delimiter used when opening or saving a Comma-separated value (.csv) file as Excel utilizes the Windows list separator character for the delimiter in .csv files. Additional resourcesAdditional resourcesIn this articleVLOOKUP Vs. INDEX/MATCH – Which One is Better? (Answered)
When it comes to the Excel world, it’s divided into two factions – the VLOOKUP brigade and the INDEX/MATCH regiment. If you’re wondering which one scores higher in the VLOOKUP vs INDEX/MATCH battle, this article will try and answer it. But before I do that, let me make a few things clear – this article is not about praising one function and bashing the other. Both of these Excel functions are amazing and have their place. In some cases, one might be better than the other. During one of my Excel Training sessions, I was surprised to find out that most of the people were aware and, in some cases, proficient in using VLOOKUP, but almost none of them knew about the powerful INDEX/MATCH combo. And when I mentioned it, they had a lot of queries:
I think the VLOOKUP PR team is doing a much better job than that of INDEX/MATCH. In this tutorial, I will try and compare these two formulas as objectively as I can.
I don’t have favorites, to begin with, but I prefer using the INDEX/MATCH combo more than VLOOKUP. The choice is driven by a lot of factors including what the data looks like and how it would be used. I will cover all these in this article. VLOOKUP Vs INDEX MATCH – An Objective ComparisonSo let’s get started and put an end to this age-long debate of VLOOKUP vs INDEX/MATCH and which one is better. And to do this, I will compare these two functions on some parameters (some are quantifiable and some are not). The popularity of the functionVLOOKUP takes this hands down. For some people, if you know how to use VLOOKUP, you know how to use Excel. And given how much can be done with this single function, I don’t blame them. For this reason, a lot of people use VLOOKUP as it is better understood by other Excel users. Although this is not a popularity contest, it plays a huge role in why VLOOKUP gets used so much. Imagine you’re working with a client/manager who is proficient in using VLOOKUP but doesn’t know how to use INDEX/MATCH. It makes more sense to use a function which both you know, instead of trying to teach them about INDEX/MATCH. Verdict: VLOOKUP is a clear winner on popularity Ease of USEThe reason VLOOKUP is so popular is that it’s easy to use (at least when compared with INDEX/MATCH). When I take Excel training, I would always start by first covering VLOOKUP first. A lot of people find VLOOKUP hard, so I can’t even imagine trying to teach them INDEX/MATCH (unless they already know how to use VLOOKUP proficiently). And in most cases, VLOOKUP is good enough. It can do most of the things people need when working with data. Surely you can do the same thing with INDEX/MATCH too, but why take the hard road when it’s not even needed. VLOOKUP takes 3 arguments (fourth is optional) and is easier to understand as compared with INDEX/MATCH. INDEX & MATCH are two separate functions that take three arguments each and should be combined to do a lookup (getting complicated already??). While you may find INDEX/MATCH equally easy when you get a hang of it, a beginner is likely to gravitate towards VLOOKUP. Verdict: VLOOKUP gets the point for its ease of use. The flexibility of the FunctionVLOOKUP is a great function but has a few limitations:
Before you start fuming with rage and leave a comment about how VLOOKUP can do all these things, hear me out. VLOOKUP, as a stand-alone function, is not meant to do this. Of course, you can combine it with other formulas and get around all these things, but then it loses the tag of being easy to use. If someone can use a combination to formulas to make VLOOKUP look to the left or make columns dynamic, I am sure that person is better off using INDEX/MATCH, which is made to handle these things with ease. So yes, VLOOKUP can get around these limitations, but that’s not worth it. With more coding and robotics, I am sure you can also make VLOOKUP fetch your favorite coffee, but remember, it’s not made for this. INDEX-MATCH combo, on the other hand, is made to handle all these issues. It can
Verdict: INDEX/MATCH combo gives you a lot more flexibility than VLOOKUP. Speed of the formulaThe difference is hardly noticeable when you have small data sets. But if you have thousands of rows and many columns, this can be a deciding factor. Without reinventing the wheel, here is a post by Charley Kyd, where he clearly mentions: At its worst, the INDEX-MATCH method is about as fast as VLOOKUP; at its best, it’s much faster. While I have considered this as one of the factors, I believe it’s less important than others. Over the years, Microsoft has been hard at work trying to improve the speed of these functions, and they have made considerable improvements since I wrote this article first. Here is an update where they mention how they are making formula such as VLOOKUP, HLOOKUP, and MATCH faster. Also, it’s a very small percentage of the number of people who can actually benefit from the speed improvement that comes with using INDEX/MATCH over VLOOKUP. Verdict: If speed is what you are looking for, INDEX/MATCH combo is the way to go. Overall Verdict (VLOOKUP Vs INDEX/MATCH Combo)Although I am a huge fan of INDEX/MATCH, in all fairness I must admit, VLOOKUP is still the King. This doesn’t mean that you should not learn about INDEX/MATCH, but if you’re new to Excel and lookup formulas, start with VLOOKUP. Use it and master it first, and then move to INDEX/MATCH INDEX/MATCH is a powerful combo that has more flexibility and speed than the VLOOKUP formula. That being said, VLOOKUP is not going anywhere and is likely to remain as one of the most popular functions in Excel for ages to come. Well, the debate is not exactly over (see the comments section). Fuel the fire – leave your 2 cents in the comments section. The Difference Between VLOOKUP and INDEX/MATCHBefore getting to the comparison, I think it’s important to know the basic difference (and more importantly the similarities) between these two functions. I am not going to get into the syntax and example of these functions. If you’re interested in that, I have detailed tutorials on both VLOOKUP and INDEX/MATCH. Both VLOOKUP and INDEX/MATCH are formulas you can use to look up a value in a dataset and fetch the corresponding value (just like you scan a menu and look for the price of the dish you want to order). In most cases, you can use these interchangeably. The main differences in these functions become more important when you need a little more than a simple lookup and fetch operation. Here are some basic differences in these two formulas (more covered in detailed later in this article):
You May Also Like the Following VLOOKUP Tutorials:
Useful Excel Resources:
Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster51 thoughts on “VLOOKUP Vs. INDEX/MATCH – Which One is Better? (Answered)”
Comments are closed. How many types of lookup functions are available in Excel quizlet?The three LOOKUP functions include VLOOKUP, HLOOKUP, and Matrix LOOKUP.
Which of the following options are examples of conditional formatting?Conditional Formatting Examples : Types. Background Color Shading (of cells). Foreground Color Shading (of fonts). Data Bars.. Icons (which have 4 different image types). Values.. What is the most efficient way to add up five different cells in a column?Use the SUM function to total a column
Click on the cell in your table where you want to see the total of the selected cells. Enter =sum( to this selected cell. Now select the range with the numbers you want to total and press Enter on your keyboard.
What is a conditional format?Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify. This changes the appearance of a cell range based on a condition (or criteria). You can use conditional formatting to highlight cells that contain values which meet a certain condition.
|