Spot the difference: Microsoft Excel’s XLOOKUP vs VLOOKUP vs INDEX/MATCH

Who is who? and what do they do? Meet Excel’s top Lookup functions.

Excel is widely known for its efficiency when navigating datasets and is an ACE tool for helping you to identify and analyse information.  

Manual searches for data can be tedious and prone to errors so utilising Excel’s top lookup functions to help you look up (see where the name comes from?) items within your data are much more effective and reliable. 

Microsoft Excel’s LOOKUP functions are among the most popular functions. These functions reference a cell to match values in another row or column against the cell, retrieving corresponding results from the respective rows and columns.

Microsoft Excel offers various lookup functions, and among them, XLOOKUP, VLOOKUP, and INDEX/MATCH stand out. 

In this article, I’ll talk you through the distinctions between Excel’s top Lookup functions and explore when to use each of them to return optimal results.

VLOOKUP

Let’s get started with VLOOKUP – a classic and reliable tool that’s been a staple part of Excel for decades.

VLOOKUP stands for vertical lookup. It’s a function that searches for a value in the first column (Excel calls this ‘leftmost’ and it’s a real pet hate of mine!) of a table and returns a corresponding value in the same row from a specified column. 

The basic syntax of VLOOKUP is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

While VLOOKUP is dependable and commonly used, it does have its limitations.

Here are some of its limitations: 

1. VLOOKUP is unable to return a value from a column prior to the ‘first column’ (this doesn’t necessarily mean the first column of your sheet – it refers to the first column of your data range. For example, if the range used in the formula is C:F, VLOOKUP will be unable to return a value from columns A or B). This means that it’s limited in its flexibility compared to XLOOKUP and INDEX/MATCH (which we’ll look at in just a moment).
2. The first column has to be the one where the match can be made, which generally means we need to amend layouts to accommodate this formula.
3. There are only two matching options (TRUE / FALSE) which provide the options of having an ‘Exact Match’ (FALSE or 0) or a ‘Nearest Match’ (TRUE or 1).

In addition to this, VLOOKUP can only be used with data that are arranged vertically – although that probably doesn’t come as a shock given that its name is literally Vertical Lookup. Of course, you could simply use its fraternal twin, HLOOKUP (Horizontal Lookup) if this was an issue.

The formula also only matches the row and we need to tell it the column to return within the formula. This makes VLOOKUP a one-dimensional formula and less dynamic than both INDEX/MATCH and XLOOKUP.

Finally, VLOOKUP is also sensitive to column rearrangements. That means that if the target column changes its position, your formula may break, leading to inaccuracies in your data.

If you want to perform a lookup that operates with more flexibility than VLOOKUP, you might want to consider INDEX/MATCH. 

INDEX/MATCH

INDEX/MATCH is the dynamic duo of Excel. It’s made up of two functions – INDEX and MATCH (another unsurprising fact) – that work together to help you retrieve data both vertically and horizontally.

The MATCH function searches for a specified value in a range and returns its relative position, while the INDEX function returns the value of a cell in a specific row and column of a range.

The basic structure of INDEX/MATCH is as follows:

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]))

INDEX/MATCH excels in scenarios where flexibility, accuracy, and performance are key.

Unlike VLOOKUP, it isn’t restricted to the first (or leftmost!) column, and it remains stable even if the table structure changes. This makes INDEX/MATCH a great choice if you’re working with dynamic datasets or if you know the data in your table will need to be updated over time.

INDEX/MATCH supports advanced lookup scenarios, such as searching based on multiple criteria. 

By nesting multiple MATCH functions within the INDEX function, you can also create complex search conditions, allowing for more precise data extraction i.e. searching for data in both rows and columns.

The downside to this function is that it requires more time to create the formula, especially if you’re working with a much larger dataset. 

TOP TIP: I always recommend using Named Ranges with an INDEX/MATCH formula, as it can make it easier to read, understand and update, while also making the formula smaller.

If you want dynamic lookup functionality, as well as speed, you’re going to love XLOOKUP.

XLOOKUP

XLOOKUP is the newest addition to the Excel Lookup family. 

Introduced as a successor to VLOOKUP and HLOOKUP, XLOOKUP is designed for enhanced flexibility and ease of use. 

With XLOOKUP, you can perform both vertical and horizontal lookups with just one function (a combination of both VLOOKUP and HLOOKUP), which eliminates the need for the separate formulas required in INDEX/MATCH.

This feature alone streamlines your Excel formulas and makes your spreadsheet more manageable.

XLOOKUP’s syntax is straightforward (honest):

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Unlike VLOOKUP, which has some significant limitations, XLOOKUP is a much more dynamic and flexible option. 

The advantages of using XLOOKUP over VLOOKUP

XLOOKUP has the ability to search from the end to the beginning of the array. This can be a game-changer when your data is organised in reverse order, as it saves having to manipulate the raw data.

Unlike VLOOKUP, XLOOKUP isn’t restricted to searching for a match from the first (leftmost – urgh!) column. 

As well as its ability to search both horizontally and vertically it can also search for matches in a column prior to the matching column. This gives you more flexibility in your lookup and allows you to widen the scope of your search to be able to find data more easily.

XLOOKUP also offers more than two matching options. When using this function you can find a match for: 

0 – Exact match. If none is found, return #N/A. (This is the default)
-1 – Exact match. If none is found, return the next smaller item.
1 – Exact match. If none is found, return the next larger item.
2 – A wildcard match where *, ?, and ~ have special meaning.

XLOOKUP also introduces the “if_not_found” parameter which allows you to specify a value or action when no match is found.

This enhances error handling and provides more control over your data retrieval process, eliminating the need to include an IFERROR formula too. 

For example, if the search value is not found in the lookup array, XLOOKUP can return a custom error message instead of the default #N/A error. This can help to prevent confusion and errors in your data analysis.

NOTE: XLOOKUP is only available in the latest versions of Microsoft Excel (2021+). 

That means that your XLOOKUP won’t work if you’re opening a sheet with an older version, so if you’re planning to share your sheets, make sure you take into consideration who it is that needs to access them before using this function.

When to Use Each Function

Now that we’ve gone through each function and have outlined their differences, you might be wondering how to choose which function is best when faced with a particular scenario. 

The answer to that isn’t quite so simple as it will depend on the specifics of your scenario, as well as your personal preferences. 

However, I’ve included a little breakdown below to help you review your scenario and pick a function to support you:

VLOOKUP

• Stick to VLOOKUP for quick and simple vertical lookups in a straightforward table.
• Use VLOOKUP when dealing with a single criterion for data retrieval.
• Exercise caution with VLOOKUP if there’s a possibility of column rearrangements in your data.
• If you’ve got the VLOOKUP formula in existing workbooks that works perfectly well, leave them there, they will continue to work just as perfectly in the future.

INDEX/MATCH

• Embrace INDEX/MATCH when flexibility and stability are paramount, especially in dynamic datasets.
• Opt for INDEX/MATCH when you need to handle advanced lookup scenarios, such as finding BOTH row and column (or Column ONLY).
• Choose INDEX/MATCH for its robustness in handling changes to the table structure.

XLOOKUP

• Use XLOOKUP when you need to perform both vertical and horizontal lookups without the hassle of separate formulas.
• Use XLOOKUP when the first column is NOT the one where a match will be found, or you need to return a result from a column prior to the matching column.
• Use XLOOKUP when you need more flexibility on the match type (i.e. Greater Than, Less Than, or even using a Wildcard character).
• Opt for XLOOKUP when searching from the end to the beginning of the array is crucial to your data analysis.
• Choose XLOOKUP when you want to handle errors more gracefully with the “if_not_found” parameter.

Summary

In summary, each of Excel’s top lookup functions – XLOOKUP, VLOOKUP, and INDEX/MATCH – has its unique strengths. 

XLOOKUP is user-friendly and versatile, VLOOKUP is a reliable classic, and INDEX/MATCH provides unparalleled flexibility and stability. 

Whether you’re embracing the modernity of XLOOKUP, relying on the classic VLOOKUP, or harnessing the power of INDEX/MATCH, Excel offers a diverse toolkit to meet your data retrieval needs.

Bookmark this article so that next time you’re faced with a lookup challenge you can refer back to it and choose the function that best suits your needs.

VLOOKUP, INDEX/MATCH, and XLOOKUP are all covered in my On Demand: Excel Formulas Workshop, along with 17 other helpful Excel Formulas. To access the workshop, click here.