Looking up information is one of the most common jobs in Excel. You might, for example, have a customer code and need to find a customer name, invoice number and a product. Or you might have a score and need the corresponding grade. 

For years, Excel users relied on VLOOKUP or the INDEX and MATCH combination. XLOOKUP brings the search and return steps together in one modern function that offers some serious flexibility.

The anatomy of XLOOKUP

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

1. lookup_value is the item you want Excel to find.
2. lookup_array is the row or column Excel searches.
3. return_array is the row or column containing the result you want back.
4. if_not_found is an optional message or value to return when no match exists.
5. match_mode controls whether Excel uses an exact, approximate or wildcard match.
6. search_mode controls the direction or method of the search.

Using required arguments (1-3), and optional argument (4), a simple lookup might be:

In the example above, Excel searches for the value in H2 (“Sarah J”) within C3:C7 and returns the corresponding value from D3:D7. If no match is found, it displays “Not found”. Here, Sarah J scored 75, so the result 75 is returned.

Using optional argument (5) to determine match mode

XLOOKUP uses an exact match by default (unlike VLOOKUP or HLOOKUP), but the optional match_mode argument gives you four choices:

Approximate matching

Approximate matches are useful for banded tables such as grades, tax rates or discounts. Suppose a grading table contains these lower boundaries:

To return the grade for a score in cell F2 in the example below, we’d use:

=XLOOKUP(F2,C2:C6,D2:D6,,-1)

Let’s say F2 contains 75, and there is no exact value of 75 in the score column. Match mode -1 tells XLOOKUP to use the next smaller item, which is 70, so the formula returns C. Keeping the thresholds in a table makes the logic easier to review and maintain than a deeply nested IF formula.

Wildcard matching: searching with only part of a value

Wildcard matching is useful when you know only part of the text you are looking for. Set match_mode to 2, then use one of Excel’s wildcard characters in the lookup value:

For example, as shown below, using a football dataset where team names sit in C2:C6 and goals scored sit in D2:D6:

=XLOOKUP(F2,C2:C6,D2:D6,”Not Found”,2)

The asterisk (*) you see after “South” in the yellow input cell (F2) means “any number of characters”. Excel therefore looks for the first team name beginning with “South” and returns that team’s goals scored. As South Africa appears before South Korea in the lookup range, South Africa is the result because XLOOKUP returns the first match it encounters by default.

A question mark matches exactly one character. For example:

=XLOOKUP(F2,C2:C6,D2:D6, “Not Found”,2)

This could match INV-10000 through INV-10009, but not INV-100000, because the question mark stands for one character only. If you genuinely need to search for an asterisk or question mark stored in the data, place a tilde before it, such as Product~*.

Using optional argument (6) search mode: controlling where Excel starts

The optional search_mode argument controls the direction or method of XLOOKUP’s search:

The most useful alternative to the default is -1, which searches from the end of the lookup array, back towards the start.

Imagine a student appears several times in a list of exam scores within a table and you want the most recent entry, with the newest scores stored at the bottom of the list:

=XLOOKUP(H2,C2:C6,F2:F6, “Not found”,,-1

The -1 in the final argument tells Excel to start at the bottom and work upwards, so the last matching record is returned without having to reverse the dataset. 

It is unlikely you will need to turn to binary search modes in your regular work, so we’ll leave those out of this article on that basis.

Returning arrays if there are multiple matches

XLOOKUP does not have to return a single cell. If the return_array covers more than one column or row, the result can spill into neighbouring cells as an array output. 

=XLOOKUP(H2,C2:C6,D2:F6, “Not Found”)

In the example above, Excel searches for the value in H2 within C2:C6, and returns matching values from columns D to F (75;82;88).

Final thoughts

XLOOKUP is one of the most versatile functions in Excel and is a “must” in every Excel user’s toolkit. INDEX / MATCH (or XMATCH) is still a firm favourite for many people, and that’s absolutely fine, but in most situations XLOOKUP does just as much, or more, within a single function.