When it comes to data analysis, there are three main types of tools that are often used: indexing, matching, and lookup. Indexing is the process of finding a specific item in a large data set by using a specific keyword or number. Matching is the process of finding all the items in a data set by using a specific criteria. Lookup is the process of finding an exact item or value within a large data set by using a specific name or number. Indexing and Matching vs. Lookup Indexing is the most common type of tool used when it comes to data analysis because it’s easy to use and can find any item you need. Indexes can be found by typing in a keyword or number into Microsoft Excel’s search bar and then clicking on the “index” button. Indexes are stored in your workbook and can be accessed anytime you want to look at your data again. Matchmaking is more complex than indexing because it requires more effort to find all the items you need for your analysis. Matchmaking works best when you have many items to analyze, but it can also be used when you only have one or two items that you want to study. To match your data, you first need to create an equation based on your information and then select one of Excel’s manymatch options (such as “All values”, “By value”, “By column”, etc.). After selecting your match option, Excel will generate lists of all possible matches for each column in your data set (or any other desired criteria). You can then click on one of these matches to see its details (such as its values, columns, etc.). Lookup is less complex than matchmaking but still requires some effort if you want to find an exact value within your data set. Lookups work best when you have many items to analyze but also want some flexibility in how you choose which values to display. To lookup, first create
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!
