Quick, clean, and to the point

XMATCH reverse search

Excel formula: XMATCH reverse search
Generic formula 

To perform a "reverse search" (i.e. search last to first), you can use the XMATCH function. In the example shown, the formula in cell G5, copied down, is:


where names (B5:B15) is a named range.


The XMATCH function offers new features not available with the MATCH function. One of these is the ability to perform a "reverse search", by setting the optional search mode argument. The default value for search mode is 1, which specifies a normal "first to last" search. In this mode, XMATCH will match the lookup value against the lookup array, beginning at the first value.

=XMATCH(F5,names,0,1) // start with first name

Setting search mode to -1 species a "last to first" search. In this mode, XMATCH will match the lookup value against the lookup array, starting with the last value, and moving toward the first:

=XMATCH(F5,names,0,-1) // start with last name

Retrieve date and amount

XMATCH returns a position. Typically, XMATCH is used with the INDEX function to return a value at that position. In the example show, we can use INDEX and XMATCH together to retrieve the date and sales for each name as follows:

=INDEX(dates,XMATCH(F5,names,0,-1)) // get date
=INDEX(sales,XMATCH(F5,names,0,-1)) // get sale

where dates (C5:C15) and sales (D5:D15) are named ranges. As before, search mode is set to -1 to force a reverse search.

For more information about using INDEX with MATCH, see How to use INDEX and MATCH.

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

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas