# XLOOKUP last match

=XLOOKUP(value,rng1,rng2,"no match",0,-1) // last exact match

To retrieve the last match in a data set with XLOOKUP, set the fifth argument to -1. In the example shown, the formula in G5, copied down, is:

=XLOOKUP(F5,item,price,0,-1)

where **item** (B5:B15) and **price** (D5:D15) are named ranges.

*beta function*, available only through the Office Insiders program.

### How this formula works

By default, XLOOKUP will return the first match in a data set. However, XLOOKUP offers an optional argument called search_mode to control the order in which data is searched. Setting search mode to -1 causes XLOOKUP to search "last to first" or, in other words, search *backwards* through the data. You can see this option used in the example shown. The formula in G5, copied down, is:

=XLOOKUP(F5,item,price,"no match",0,-1)

The same formula without named ranges is:

=XLOOKUP(F5,$B$5:$B$15,$D$5:$D$15,"no match",0,-1)

XLOOKUP's arguments are configiured as follows:

- The
*lookup_value*comes from cell F5 - The
*lookup_array*is the named range**item**(B5:B15) - The
*return_array*is the named range**price**(D5:D15) - The
*not_found*argument is provided as "no match" - The
*match_mode*is set to 0 (exact match) - The
*search_mode*is set to -1 (last to first)

At each row, XLOOKUP looks for the item name in column F in B5:B15, starting at the *bottom*. When an exact match is found, the corresponding price in column D is returned. If no match is found, XLOOKUP will return #N/A.

*Note: this example depends on data being sorted by date in ascending order. If data is unsorted, see this example.*

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.