The Best Lookup Functions in Excel: Examples, Pros, and Cons

There are many ways to find and retrieve data from a table or range based on a lookup value. In fact, because Microsoft often comes up with new, modernized alternatives, there are too many ways! So, here are the three I use the most.

XLOOKUP: A Modern Take on VLOOKUP and HLOOKUP

XLOOKUP is arguably the mother of all lookup functions in Excel, which is surprising given it took Microsoft over 30 years to come up with it!

It returns one or more items from a range or array according to the first or last match it finds. Because it works vertically and horizontally, looks up things to the left, right, above, or below, and can return a whole column or row, it completely replaces its predecessors, VLOOKUP and HLOOKUP. You can also specify what value the formula returns if there’s no match.

That said, if you’re using a version of Excel released before 2021, you won’t be able to use the XLOOKUP function.

The XLOOKUP Syntax

XLOOKUP has six arguments:

=XLOOKUP(a,b,c,d,e,f)

where

• a (required) is the lookup value,
• b (required) is the lookup array,
• c (required) is the return array,
• d (optional) is the text to return if the lookup value (a) is not found in the lookup array (b),
• e (optional) is the match mode (0 = exact match (default), -1 = exact match or next smaller item, 1 = exact match or next larger item, 2 = wildcard match), and
• f (optional) is the search mode (1 = first to last (default), -1 = last to first, 2 = binary search where b is in ascending order, -2 = binary search where b is in descending order).

Arguments a and c must be the same height for vertical lookups or the same width for horizontal lookups.

The XLOOKUP Function in Action

XLOOKUP can return a single value based on a single criterion. Here, the formula in cell H3 returns the player’s score according to the ID in cell H1:

=XLOOKUP(H1,Players(ID),Players(Score),"No ID")

In this case, there’s no need to enter arguments e (match mode) or f (search mode), as the default options (exact match and a top-to-bottom search) are what you need.

XLOOKUP can also be used to return results from multiple adjacent cells. Here, the formula in cell G4 returns a player’s gender, country, team, and score when you input the ID, with the lookup value (argument c) comprising the first column, then a colon, then the last column:

=XLOOKUP(H1,Players(ID),Players((Gender):(Score)))

To use the XLOOKUP function to return values from non-adjacent columns or rows, you need to nest the FILTER function in the return array argument, with 0s and 1s in curly braces telling Excel which columns to return. This formula looks up the ID in cell H1, and returns the corresponding Country (third column) and Score (fifth column):

=XLOOKUP(H1,Players(ID),FILTER(Players,{0,0,1,0,1}))

Finally, you can combine XLOOKUP with logical operators to return a value based on multiple criteria. This formula tests whether each criterion in the lookup array (argument b) is true, returning 1 if it is or 0 if it isn’t. The first match is returned if all criteria are true (in other words, the result of the lookup array calculation equals the lookup value (1):

=XLOOKUP(1,(Players(Gender)=H1)*(Players(Country)=H2),Players(ID))

Since the Score column is sorted in descending order, the result is the ID of the highest-scoring female Canadian.

To find the lowest-scoring female Canadian, reverse the sort order of the Score column or type -1 for the search mode (argument f).

Summary: The Pros and Cons of XLOOKUP

Here are the benefits and drawbacks of this useful function:

INDEX With XMATCH: A More Powerful Alternative to INDEX With MATCH

Many people who have used Excel for years still perform lookups using the old-school INDEX-MATCH combo, primarily because it’s more flexible than VLOOKUP and XLOOKUP. However, combining INDEX with XMATCH, the updated version of MATCH, gives you more options.

INDEX with XMATCH returns an item from a range or array according to the first or last match it finds. Like XLOOKUP, it works with vertical and horizontal datasets, can search in any direction, and can be used to return whole columns or rows.

However, because XMATCH is newer than MATCH, it’s only available to those using Excel for the web or desktop versions of Excel released in 2021 or later.

These two functions are commonly used together because the INDEX identifies the lookup column, and XMATCH identifies the lookup row.

The INDEX-MATCH Syntax

Since I’m going to show you how to combine these functions in your lookup, here’s the combined syntax for vertical (the most common) lookups:

=INDEX(a,XMATCH(b,c,d,e),f)

• a (required) is the array or name of the table where the return value is located.
• b (required) is the lookup value,
• c (required) is the column containing the lookup value,
• d (optional) is the match mode (0 = exact match (default), -1 = exact match or next smallest item, 1 = exact match or next largest item, 2 = wildcard match, 3 = regex match),
• e (optional) is the search mode (1 = first to last (default), -1 = last to first, 2 = binary search where b is in ascending order, -2 = binary search where b is in descending order), and
• f (required) is the column number of the return value.

In other words, the XMATCH arguments (b, c, d, and e) tell the INDEX function which row in the array or table to look in, and the final argument of the INDEX function (f) identifies a column. Together, they tell Excel which cell to look in to return the correct value.

The INDEX-XMATCH Function Combination in Action

You can use INDEX with XMATCH to return a single value based on a single criterion. In this example, the pairing is used to return the score (column 5 in the Players table) of a player whose ID is in cell H1:

=INDEX(Players,XMATCH(H1,Players(ID)),5)

Notice how, although the syntax is initially quite complicated, if you want to return an exact match in a search that looks from top to bottom, you can omit two of the arguments.

If there’s no match, Excel returns an #N/A error. To avoid this, use Data Validation to create a drop-down list of options for the lookup value. Alternatively, embed the whole formula inside an IFERROR formula:

=IFERROR(INDEX(Players,XMATCH(H1,Players(ID)),5),"No match")

The issue with the above examples is that the column number is hard-coded into the formula. Instead, you can embed a second XMATCH set of arguments to return this value through a two-way lookup:

=INDEX(Players,XMATCH(H1,Players(ID)),XMATCH(G3,Players(#Headers)))

Here, the column number is identified by matching the variable in G3 to the column headers in the Players table.

You can also use INDEX with XMATCH to return a value based on more than one criterion by combining them with logical operators. Having sorted column E in descending order, I can use this formula to return the ID of the highest-scoring female in team C:

=INDEX(Players(ID),XMATCH(1,(Players(Gender)=H1)*(Players(Team)=H2)))

To find the lowest-scoring female in team C, reverse the sort order of column E or type -1 for the search mode (argument e).

Summary: The Pros and Cons of INDEX With XMATCH

If you’re still not sure whether these functions are the ones for you, here’s a summary of their pros and cons:

FILTER: A Simple Function That Returns All Matching Values

Whereas XLOOKUP and INDEX with XMATCH return a single match, Excel’s FILTER function returns all matches—a good reason to choose it over the others. However, only those using Excel 2021 or later, Excel for the web, or the Excel mobile app can use this function.

The FILTER Syntax

Here’s how the FILTER function works:

=FILTER(a,b,c)

where

• a (required) is the array containing the values you want to return,
• b (required) is the inclusion criterion that determines the filter, and
• c (optional) is the text to return if no values match the inclusion criteria (b).

Arguments a and b must reference arrays that are the same size as each other.

The FILTER Function in Action

You can use the FILTER function to return an array from a column based on a single criterion. This formula, typed into cell H3, returns the IDs of all players whose country matches the value in cell H1, returning “No match” instead of an error if there are no matches:

=FILTER(Players(ID),Players(Country)=H1,"No match")

The filtered values are returned in the order they appear in the source data.

In the example above, only the ID column of the Players table was selected as the return array. However, you can also use the FILTER function to return corresponding data from all columns. After typing the column headers manually in cells G3 to K3, in cell G4 I typed:

=FILTER(Players,Players(Country)=H1,"No match")

where Players (argument a) is the table name, meaning all columns are returned in the filtered result.

Finally, you can use FILTER to return values that match more than one condition by using logical operators. This formula returns the data for all females in team C:

=FILTER(Players,(Players(Gender)=H1)*(Players(Team)=H2),"No match")

Summary: The Pros and Cons of FILTER

Here’s an overview of the benefits and drawbacks of Excel’s FILTER function:

Although not strictly lookup functions, Excel’s CHOOSECOLS and CHOOSEROWS functions are ideal if you want to quickly extract specific columns or rows from your data.