Index and Match vs. VLOOKUP vs. XLOOKUP in Microsoft Excel When it comes to lookup functions in Microsoft Excel, there are three main options: Index and Match, VLOOKUP, and XLOOKUP. Each has its own strengths and weaknesses, so it’s important to know which one is best for the task at hand. Index and Match is the simplest of the three options, as it simply looks up a value in a specific column of data. This is great for simple tasks like finding the value for a specific customer name or product number. However, Index and Match has one major limitation: it can only look up values within the same row as the lookup column. If you need to lookup a value across multiple rows, VLOOKUP or XLOOKUP are your best options. VLOOKUP is similar to Index and Match in that it looks up values within a specific column. However, VLOOKUP also allows you to look up values across multiple columns, making it more versatile than Index and Match. Additionally, VLOOKUP can handle complex search criteria better than either Index and Match or XLOOKUP. XLOOKUP is by far the most powerful of the three lookup functions available in Microsoft Excel. It allows you to look up any value in any column or row of data, making it perfect for tasks like finding sales figures by product category or customer name. Additionally, XLOOKUP can handle complex search criteria better than either VLOOKUP or Index and Match. However, XLookup does have one major limitation: it can only return single values (not arrays). If you need to return multiple values from a lookup table (like with an order form), then using an array formula will be your best option ..
Lookup functions in Microsoft Excel are ideal for finding what you need when you have a large amount of data. There are three common ways to do this; INDEX and MATCH, VLOOKUP, and XLOOKUP. But what’s the difference?
INDEX and MATCH, VLOOKUP, and XLOOKUP each serve the purpose of looking up data and returning a result. They each work a bit differently and require a specific syntax for the formula. When should you use which? Which is better? Let’s take a look so you know 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 take a look at our how-tos for the INDEX function and MATCH function for specific details on using them individually.
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’ll have a syntax like this: INDEX(return_array, MATCH(lookup_value, lookup_array)) in its most basic form. It’s easiest to look at some examples.
To find a value in cell G2 in the range A2 through A8 and provide the matching result in the range B2 through B8, you would use this formula:
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 lookup value:
Our result is Houston for both formulas.
We also have a tutorial that goes into detail on using INDEX and MATCH should that be your choice.
RELATED: How to Use INDEX and MATCH in Microsoft Excel
Using VLOOKUP
VLOOKUP has been a popular reference function in Excel for some time. The V stands for Vertical, so with VLOOKUP, you’re doing a vertical lookup and it’s from left to right.
The syntax is VLOOKUP(lookup_value, lookup_array, column_number, range_lookup) with the last argument optional as True (approximate match) or False (exact match).
Using the same data as that for INDEX and MATCH, we’ll look up the value in cell G2 in the range A2 through D8 and return the value in the second column that matches. You’d use this formula:
As you can see, the result using VLOOKUP is the same as using INDEX and MATCH, Houston. The difference is that VLOOKUP uses a much simpler formula. For more details on VLOOKUP, check out our how-to.
RELATED: How to Use VLOOKUP on a Range of Values
So why would anyone use INDEX and MATCH instead of VLOOKUP? The answer is because VLOOKUP only works when your lookup value is to the left of the return value you want.
If we did the reverse and wanted to look up a value in the fourth column and return the matching value in the second column, we would not receive the result we want and may even receive an error. As Microsoft writes:
INDEX and MATCH covers the entire cell range or array making it a more robust lookup option even if the formula is a bit more complicated.
Using XLOOKUP
XLOOKUP is a reference function that arrived in Excel after VLOOKUP and the counterpart HLOOKUP (horizontal lookup). The difference between XLOOKUP and VLOOKUP is that XLOOKUP works no matter where the lookup and return values reside in your cell range or array.
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 that in the VLOOKUP function. XLOOKUP offers three optional arguments at the end for giving a text result if the value isn’t found, a mode for the type of match, and a mode for how to perform the search.
For the purpose of this article, we’ll concentrate on the first three required arguments.
Back to our cell range from earlier, we’ll look up the value in G2 in the range A2 through A8 and return the matching value from the range B2 through B8 with this formula:
And like with INDEX and MATCH as well as VLOOKUP, our formula returned Houston.
We can also use a value in the fourth column as the lookup value and receive the correct result in the second column:
With this in mind, you can see that XLOOKUP is a better option than VLOOKUP simply because you can arrange your data any way you like and still receive your desired result. For a full tutorial on XLOOKUP, head over to our how-to.
RELATED: How to Use the XLOOKUP Function in Microsoft Excel
So now you’re wondering, should I use XLOOKUP or INDEX and MATCH, right? Here are some things to consider.
Which Is Better?
If you already use the INDEX and MATCH functions separately and have used them together to lookup values, then you may be more familiar with how they work. By all means, if it’s not broken, don’t fix it, and keep using what makes you comfortable.
And of course, if your data is structured to work with VLOOKUP and you’ve used that function for years, you can continue using it or make the easy transition to XLOOKUP leaving INDEX and MATCH in the dust.
If you want to a simple, easy-to-build formula in any direction, XLOOKUP is the way to go and can replace INDEX and MATCH. You don’t have to worry about combining arguments from two functions into one or rearranging your data.
One last consideration, XLOOKUP does offer those three optional arguments which may come in handy for your needs.
Over to you! Which lookup option will you use in Microsoft Excel? Or maybe, you’ll use all three depending on your needs? No matter what, it’s nice to have options!
