XLOOKUP and INDEX MATCH are Excel functions for performing lookups.
The functions retrieve values from a table or range but differ in functionality, syntax, flexibility, availability, performance, ease of use, etc.
For decades, Excel users have preferred INDEX MATCH to VLOOKUP because of its greater flexibility.
For instance, while VLOOKUP can only search for a term in the leftmost column of a table or data range and return values from columns to its right, INDEX MATCH allows you to search in any column and return a result in the same row from any other column, regardless of its position to the left or right of the lookup column.
However, in 2019, Microsoft released XLOOKUP to replace VLOOKUP and the INDEX MATCH combination.
XLOOKUP provides a more straightforward approach to lookups with additional features like error handling and has been dubbed the ‘slayer of VLOOKUP’ by many Excel enthusiasts. Has XLOOKUP also ‘slayed’ INDEX MATCH? You will find out in this tutorial.
Note: In 2019, Microsoft also released the XMATCH function, an enhanced version of MATCH.
In this tutorial, I will compare XLOOKUP and INDEX MATCH in terms of availability, functionality, syntax, flexibility, performance, ease of use, etc. This information will help you understand when to use each and whether XLOOKUP has also ‘slayed’ INDEX MATCH.
But first, let’s look closely at each function, starting with the older INDEX MATCH.
Click here to download the example file and follow along
INDEX MATCH
INDEX MATCH combines INDEX and MATCH functions to return a value at the intersection of a particular row and column in a given table or data range.
The table below provides the syntaxes of INDEX, MATCH, and INDEX MATCH functions.
Function | Syntax |
---|---|
INDEX | INDEX(array, row_num, [column_num]) |
MATCH | MATCH(lookup_value, lookup_array, [match_type]) |
INDEX MATCH | INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num]) |
From the above table, in the INDEX MATCH combination, the job of the MATCH function inside the INDEX function is to supply a value for the row_num argument of the INDEX function.
Note: The MATCH function can also supply a value for the column_num argument, for example, in two-way lookups.
So that you gain a thorough understanding of how the INDEX MATCH combination operates, let’s examine each function in detail.
The INDEX Function
The INDEX function is available in all versions of Excel.
The INDEX function retrieves the value at the intersection of a specified row and column in a given table or data range.
Syntax of INDEX:
INDEX(array, row_num, [column_num])
The function takes three arguments. The first two arguments are required and the last is optional.
The table below describes each argument.
Argument | Description |
---|---|
array | A range of cells or an array constant from which you want to retrieve data.If the array contains only one row or column, the corresponding row_num or column_num argument is optional.If the array has more than one row and more than one column, and you supply only row_num or column_num, INDEX returns an array of the entire row or column. |
row_num | The row number in the cell range or array from which you want to return a value. If the cell range or array has multiple rows, this argument is required. It is not needed if the cell range has only one row. If you omit it, you must supply the column_num argument. |
column_num | The column number in the cell range or array from which you want to return a value. If the array has only one column, this argument is not required. If you omit it, you must supply the row_num. |
Example of the INDEX Function
In the example below, the INDEX function retrieves the value at the intersection of the fourth row and third column in the given table.
Notice that if you use the INDEX function alone to retrieve a value from a table or data range, you must know the exact row and column numbers to retrieve the correct value.
If you want to retrieve a value based on a condition (e.g., finding a country’s code by its name), INDEX alone won’t help because it can’t search for the row number by itself. Combining INDEX with MATCH helps in such cases where MATCH finds the row number dynamically.
The MATCH Function
The MATCH function is available in all versions of Excel.
The MATCH function finds the position of a specific value within a range of cells. It returns the relative position of the lookup value in a given range, rather than the actual value itself.
Syntax of MATCH:
MATCH(lookup_value, lookup_array, [match_type])
The function takes three arguments. The first two arguments are required and the last one is optional.
The table below describes each argument.
Argument | Description |
---|---|
lookup_value | The value you want to search for in the target range. It can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. |
lookup_array | The range of cells where you want to search the look_up value. |
match_type | Specifies how the function will match the lookup value. It can be:1 or omitted – Find the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, otherwise the function returns invalid results.0 – Find the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.-1 – Find the smallest value greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, otherwise the function returns invalid results. |
Example of the MATCH Function
In the example below, the MATCH function finds the relative position of a country in the ‘Country’ column of the table.
Now that we have looked at the INDEX and MATCH functions individually, let me give you an example of INDEX MATCH.
Example of INDEX MATCH
In the example below, INDEX MATCH finds the row where the country in E2 is located within the ‘Country’ column of the ‘Table148,’ and returns the value (country code) in the third column in the same row.
Note: I set the third argument of the MATCH function (match_type) to 0 to ensure an exact match, which is usually the preferred behavior.
XLOOKUP Function
XLOOKUP function is only available in Excel 365 and later.
The XLOOKUP function searches a cell range or an array for a match and returns the corresponding item from a second range or array. It uses an exact match by default.
In a table or a data range, XLOOKUP looks in one column for a search term and returns a result in the same row in another, regardless of which side the return column is on.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XLOOKUP takes six arguments. The first three are required and the rest are optional.
The table below describes each argument.
Argument | |
---|---|
lookup_value | The value you are searching for. |
lookup_array | The range or array where you want to search for the lookup value. |
return_array | The range or array that contains the value you want to return. |
[if_not_found] | The text value to return if the lookup value is not found. If you omit it, and the lookup value is not found, Excel returns a #N/A error. |
[match_mode] | The type of match to perform:0 – Exact match. If not found, return #N/A or the [If_not_found] value. 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 * (asterisk) finds any number of characters,? (question mark) finds any single character, and ~ (tilde) finds a question mark, tilde, or asterisk. |
[search_mode] | The search direction:1 – Perform a search starting at the first item. This is the default mode.-1 – Perform a reverse search starting at the last item.2 – Perform a binary search depending on ‘lookup_array’ being sorted in ascending order. If not sorted, invalid results are returned.-2 – Perform a binary search depending on lookup_array being sorted in descending order. If not sorted, invalid results are returned. |
An Example of XLOOKUP
In the example below, XLOOKUP looks up a country name in a range and returns its country code.
The lookup_value is in cell E2, the lookup_array is the cell range A2:A9, the return_array is the cell range C2:C9, and the if_not_found value is the text string ‘Not found.’ The match_mode argument is omitted because XLOOKUP uses exact match by default. The search_mode argument is omitted because XLOOKUP performs a search starting at the first item by default.
Comparing XLOOKUP and INDEX MATCH – Examples
In this section, I will show you some examples comparing XLOOKUP and INDEX MATCH.
Example #1: Basic Lookup
Suppose you have a list of countries in column A and their corresponding country codes in column C. You want to retrieve the country code of a particular country, say in cell E4.
The INDEX MATCH formula to return the country code is:
=INDEX(A3:C10,MATCH(E4,A3:A10,0),3)
The XLOOKUP formula to retrieve the country code is:
=XLOOKUP(E8,A3:A10,C3:C10)
Both formulas achieve the same result as shown in the screenshot below.
The XLOOKUP formula is more straightforward than the INDEX MATCH formula.
Also read: Find the Closest Match in Excel (Nearest Value)
Example #2: Left Lookup
Suppose you have a list of country codes in column C and their corresponding country names in column A. You want to retrieve the country name of a particular country code, say in cell E4.
The INDEX MATCH formula to return the country name is:
=INDEX(A3:C10,MATCH(E4,C3:C10,0),1)
The XLOOKUP formula to retrieve the country name is:
=XLOOKUP(E8,C3:C10,A3:A10)
Both formulas achieve the same result as shown in the screenshot below.
XLOOUP and INDEX MATCH can perform left lookups. However, the XLOOKUP formula is more straightforward.
Example #3: Horizontal Lookup
Suppose you have a list of country names in row 3 and their abbreviations in row 4. Here, I want to retrieve the country name abbreviation of a particular country name, say in cell A8.
The INDEX MATCH formula to return the country name abbreviation is:
=INDEX(B4:I4,MATCH(A8,B3:I3,0))
The XLOOKUP formula to retrieve the country name abbreviation is:
=XLOOKUP(A12,B3:I3,B4:I4)
Both formulas achieve the same result as shown in the screenshot below.
XLOOUP and INDEX MATCH can perform horizontal lookups. However, the XLOOKUP formula is more straightforward and intuitive.
Also read: Find Last Occurrence of a Value in a Column in Excel
Example #4: Two-way Lookup
A two-way lookup, often referred to as a ‘matrix’ or ‘2-dimensional lookup,’ finds a value located at the intersection of a specific row and column.
Suppose you have a list of student names in column A and their scores for MS Excel, MS Access, and MS PowerPoint in columns B, C, and D respectively. You want to retrieve a student’s score in MS PowerPoint. The student’s name is in cell G3 and the subject is in cell G4.
The INDEX MATCH formula to return the student’s score is:
=INDEX(B3:D12, MATCH(G3, A3:A12, 0), MATCH(G4, B2:D2, 0))
The XLOOKUP formula to retrieve the student’s score is:
=XLOOKUP(G3, A3:A12, XLOOKUP(G4, B2:D2, B3:D12))
Both formulas achieve the same result as shown in the screenshot below.
XLOOKUP and INDEX MATCH can perform 2-dimensional lookups. However, INDEX MATCH is well suited for this kind of lookup because you can use 2 MATCH functions to retrieve the row and column indexes.
When performing a 2-dimensional lookup with XLOOKUP, it becomes slightly more complicated since XLOOKUP is mainly intended for a single lookup array. To handle rows and columns simultaneously, you must nest one XLOOKUP function inside another.
The inner XLOOKUP searches for its value and returns an array of related data (a row or column), which then serves as the return_array for the outer XLOOKUP.
Example #5: Return Values From Non-adjacent Columns
Suppose you have a list of student names in column A and their scores for MS Excel, MS Access, and MS PowerPoint in columns B, C, and D respectively.
You want to retrieve a student’s scores in MS Excel and MS PowerPoint. The student’s name is in cell G3.
The INDEX MATCH array formula to return the student’s scores is:
=INDEX(B3:D12, MATCH(G3, A3:A12, 0), MATCH(F6:G6, B2:D2, 0))
The XLOOKUP array formula to retrieve the student’s score is:
=XLOOKUP(G3, A3:A12, FILTER(B3:D12, {1,0,1}))
Both formulas achieve the same result as shown in the screenshot below.
XLOOKUP and INDEX MATCH can return values from multiple non-adjacent columns.
INDEX MATCH utilizes a 2-dimensional approach where the first MATCH finds the right row number for the student name in G3, and the second MATCH finds the column numbers for the subjects in B2:D2.
Note: If you have an older version of Excel, press CTRL + Shift + Enter to enter the INDEX MATCH formula.
To retrieve values from non-adjacent columns using XLOOKUP, combine it with the FILTER function.
By adding the horizontal array {1,0,1} to the ‘include’ argument of ‘FILTER’ you specify which columns to return (represented by ‘1’) and which to exclude (represented by 0).
XLOOKUP is not as flexible as INDEX MATCH in returning values from non-adjacent columns.
Example #6: Handling Missing Values
Suppose you have a list of countries in column A and their corresponding country codes in column C. You want to retrieve the country code of a particular country, say in cell E4.
The INDEX MATCH formula (shown below) returns an #N/A error when it does not find the specified lookup value.
To handle this, you can wrap the formula in the IFNA or IFERROR function and display a custom message instead.
For example, to return your custom text message instead of an #N/A error, you can use INDEX MATCH together with IFNA:
=IFNA(INDEX(A3:C10,MATCH(E4,A3:A10,0),3),"Not found")
When you use XLOOKUP, include your custom message in the fourth argument:
=XLOOKUP(E8,A3:A10,C3:C10,"Not found")
Both formulas handle missing values gracefully.
XLOOKUP has a built-in error-handling option, the if_not_found argument. INDEX MATCH requires using functions like IFERROR or IFNA to handle errors, which adds extra steps.
XLOOKUP Vs INDEX MATCH – Comparison Summary
Feature | XLOOKUP | INDEX MATCH |
---|---|---|
Availability | Only available in Excel 365 and later. | Available in all versions of Excel.Note: XMATCH, an enhanced version of MATCH is available only in Excel 365 and later. |
Ease of Use | A single straightforward function, easier to type and understand. | Combines two functions, not straightforward. |
Error Handling | Provides a built-in error handling option with the ‘if-not-found’ argument, making it easy to specify a fallback value without additional functions. | Requires using functions like IFERROR or IFNA to handle errors, which adds extra steps. |
Performance | Generally faster in large datasets since it’s optimized for newer versions of Excel, needing only one function call to perform lookup and return. | Performs well but can be slower on large datasets due to the need for two separate function calls. |
Approximate and Exact Match | Supports exact and approximate matches with built-in options in the search_mode argument. | Supports exact and approximate matches with built-in options in the match_type argument of the MATCH function. |
Multiple Matches | Returns multiple results for a single lookup value when you use array formulas. | Returns multiple results for a single lookup value when you use array formulas. |
Reverse Order Search | Has native support for searching in reverse order. | Doesn’t have native support for searching in reverse order. |
Wildcard Support | Has native support for wildcard characters, * (asterisk),? (question mark), and ~ (tilde) for partial matching. | Supports wildcard characters, but only when performing a text match and using approximate matches. |
Binary Search | Binary search is achieved through the search_mode argument you specify directly in the function. | Binary search is achieved through the match_type argument of the MATCH function. |
Two-way Lookups | To handle two-way lookups you must nest one XLOOKUP function inside another. | Best suited for two-way lookups because you can use 2 MATCH functions to retrieve the row and column indexes. |
Choosing Between XLOOKUP and INDEX MATCH
If you have Excel 365 or later, XLOOKUP is generally more flexible and easier to use than INDEX MATCH.
XLOOKUP simplifies lookups and error handling and generally offers faster performance. However, there are some situations where INDEX MATCH may offer more flexibility, such as when returning values from non-adjacent columns.
If you have an older version of Excel or need to ensure compatibility across different versions, INDEX MATCH remains a powerful option.
However, it requires more setup and is generally less intuitive compared to XLOOKUP.
In this tutorial, I have compared XLOOKUP with INDEX MATCH. I hope you found the tutorial helpful.
Other Excel articles you may also like: