Search functions in Microsoft Excel are great for finding what you need when you have a lot of data. There are three common ways to do this: INDEX and MATCH, VLOOKUP and XLOOKUP. But what is the difference?
INDEX and MATCH, VLOOKUP and XLOOKUP all aim to search for data and return a result. They each work a little differently and require a specific syntax for the formula. When should one or the other be used? Which is the best? Let’s take a look to find out which is the best option for you.
Using INDEX and MATCH
Obviously, the INDEX and MATCH combination is a mixture of the two named functions. You can refer to our INDEX and MATCH function manuals for specific details on their individual use.
To use this duo, the syntax for each is INDEX(array, row_number, column_number) and MATCH(value, array, match_type).
When you combine the two, you get syntax like this: INDEX(return_array, MATCH(lookup_value, lookup_array)) in its most basic form. The easiest way is to look at a few examples.
To find a value in cell G2 in the range A2 to A8 and provide the corresponding result in the range B2 to B8, you would use this formula:
INDEX and MATCH with a cell reference
If you prefer to insert the value you want to find instead of using the cell reference, the formula looks like this where 2B is the search value:
Our result is Houston for both formulas.
INDEX and MATCH with a value
We also have a tutorial that explains in detail how to use INDEX and MATCH if you want.
VLOOKUP has been a popular reference function in Excel for quite some time. The V stands for Vertical, so with VLOOKUP you search vertically, left to right.
The syntax is: VLOOKUP(lookup_value, lookup_array, column_number, range_lookup), with the last argument optional: True (approximate match) or False (exact match).
Using the same data as for INDEX and MATCH, we will look up the value of cell G2 in the range A2 to D8 and return the value of the second column that matches. You will use this formula:
VLOOKUP with a cell reference
As you can see, the result obtained using VLOOKUP is the same as that obtained using INDEX and MATCH. The difference is that VLOOKUP uses a much simpler formula. For more details on the VLOOKUP, see our user guide.
So why use INDEX and MATCH instead of VLOOKUP? The answer is that VLOOKUP only works when the lookup value is to the left of the desired return value.
If we did the reverse and wanted to look up a value in the fourth column and return the corresponding value in the second column, we wouldn’t get the desired result and might even receive an error. As Microsoft writes:
N'oubliez pas que la valeur de consultation doit toujours se trouver dans la première colonne de la plage pour que VLOOKUP fonctionne correctement. Par exemple, si votre valeur de recherche se trouve dans la cellule C2, votre plage doit commencer par C.
INDEX and MATCH cover the entire range of cells or table, making them a more robust lookup option, even if the formula is a bit more complicated.
XLOOKUP is a reference function that arrived in Excel after VLOOKUP and its counterpart HLOOKUP (horizontal lookup). The difference between XLOOKUP and VLOOKUP is that XLOOKUP works no matter where the reference and return values are in your cell range or table.
The syntax is XLOOKUP(lookup_value, lookup_array, return_array, not_found, match_mode, search_mode). The first three arguments are required and are similar to those of the VLOOKUP function. XLOOKUP offers three optional arguments at the end to give a textual result if the value is not found, a mode for the type of match, and a mode for how to perform the search.
For the purposes of this article, we will focus on the first three mandatory arguments.
Let’s go back to our range of cells from earlier. We will search for the value of G2 in the range A2 to A8 and return the corresponding value in the range B2 to B8 with the following formula:
XLOOKUP with a cell reference
And as with INDEX and MATCH as well as VLOOKUP, our formula turned Houston around.
We can also use a value from the fourth column as the search value and get the correct result in the second column:
XLOOKUP from right to left
Keeping this in mind, you can see that XLOOKUP is a better option than VLOOKUP simply because you can arrange your data the way you want and get the desired result. For a full tutorial on XLOOKUP, check out our how-to guide.
Now you’re wondering if you should use XLOOKUP or INDEX and MATCH, right? Here are some things to consider.
What is the best choice ?
If you already use the INDEX and MATCH functions separately and have used them together to find values, you may be more familiar with how they work. If it ain’t broke, don’t fix it, and keep using what works for you.
And of course, if your data is structured to work with VLOOKUP and you’ve been using this feature for years, you can continue to use it or make an easy transition to XLOOKUP leaving INDEX and MATCH in the dust.
If you want a simple formula that’s easy to build in any direction, XLOOKUP is the way to go and can replace INDEX and MATCH. You don’t have to worry about combining the arguments of two functions into one or rearranging your data.
One last consideration, XLOOKUP offers these three optional arguments which may be useful for your purposes.
It’s your turn ! Which lookup option will you use in Microsoft Excel? Or maybe you will use all three depending on your needs? Either way, it’s good to have options!