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:


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

Generic formula

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


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 configured 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.

Dynamic Array Formulas are available in Office 365 only.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.