Excel MATCH in Reverse Order: Find the Last Match

Formulas & functions · Updated November 19, 2026

Excel’s MATCH function does not have a setting to search from bottom to top. To find the last matching item in a range, use XMATCH with a search mode of -1, or use XLOOKUP with a search mode of -1 to return a related value as well. These options search from the end of the range toward the beginning.

What “reverse order” means in Excel

A reverse-order lookup starts at the end of a range and moves upward, or from right to left in a horizontal range. Its main use is finding the last occurrence of a value when the same item appears more than once. For example, a customer may appear in several transaction rows, and you may want the customer’s most recent entry as it appears in the worksheet.

That is different from sorting the data in descending order. A reverse search leaves the worksheet as it is and changes which matching occurrence Excel finds. It is also different from reversing a list’s displayed order. Those are separate tasks, and confusing them can lead to a result that looks right but answers a different question.

“Last” means last in the range’s current order, not necessarily latest by date. If dates are not arranged chronologically, a reverse search will not identify the greatest date. It simply finds the matching cell closest to the bottom of the specified range, or closest to the right for a horizontal range.

Use XMATCH to find the last matching position

XMATCH is the closest modern equivalent to MATCH when you need to control search direction. Its fourth argument, search_mode, can be set to -1 for a last-to-first search. With an exact match mode, the result is the position of the last matching value within the lookup range—not the worksheet row number.

For example, assume product names are in A2:A8 and the name to find is in E2. The list contains “Tea” in A3 and A7. This formula returns 6, because A7 is the sixth item in the range A2:A8:

With product names in A2:A8 and the search term in E2, use: =XMATCH(E2,A2:A8,0,-1)

The arguments are the value to find, the lookup range, the match mode, and the search mode. A match mode of 0 asks for an exact match; a search mode of -1 searches last to first. If the item is not found, XMATCH returns the #N/A error unless you handle it with another function such as IFNA.

XMATCH is available in Microsoft 365 and Excel 2021 or later. In Excel for the web, it is also available. The formula is the same in Excel for Windows and Mac; regional settings can affect whether a workbook uses commas or semicolons between arguments.

Use XLOOKUP to return the last matching value

If you need a value from another column rather than a position, XLOOKUP is often the more direct choice. Set its match mode to 0 and its search mode to -1. Excel then searches the lookup range from the end and returns the corresponding value from the return range.

Suppose transaction names are in A2:A100, amounts are in B2:B100, and the name to look up is in E2. If “Mira” appears more than once, this returns the amount from the last row containing Mira:

With names in A2:A100, amounts in B2:B100, and the lookup name in E2, use: =XLOOKUP(E2,A2:A100,B2:B100,"Not found",0,-1)

In this formula, “Not found” is the optional result shown when there is no match. The 0 specifies exact matching, and -1 specifies a last-to-first search. For instance, if the matching amounts are 18 and 24 and the row containing 24 occurs later in the range, the result is 24.

Keep the lookup and return ranges the same size and aligned row by row. If the lookup range begins at row 2 but the return range begins at row 3, Excel may return a value from the wrong record. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Older Excel versions may not recognize it.

How MATCH differs from XMATCH

MATCH can find a position, but it does not include a search-direction argument. With exact matching, it returns the first match it encounters as it scans the range in its normal order. If a value occurs several times, MATCH does not offer a built-in way to request the last occurrence.

For example, with product names in A2:A8 and the search term in E2, this returns the position of the first exact match:

With product names in A2:A8 and the search term in E2, use: =MATCH(E2,A2:A8,0)

A common misunderstanding is that MATCH’s third argument of -1 means “search backward.” It does not. That argument is match_type: -1 asks MATCH to find the smallest value that is greater than or equal to the lookup value, and the lookup range must be sorted in descending order. A match_type of 1 has its own approximate-match behavior and requires ascending order. For a normal exact lookup, use 0.

XMATCH separates these two ideas: its third argument controls the kind of match, while its fourth controls search direction. That makes XMATCH the natural replacement when you want MATCH-style position results but need to search backward.

Choose the right method for your Excel version

For Microsoft 365 or Excel 2021 and later, choose XMATCH when you need a position, and XLOOKUP when you need a corresponding value. Both make the reverse-search instruction explicit, and both support exact matching without sorting the data.

For older Excel versions without those functions, LOOKUP can be used for a last-match lookup. It is a longstanding technique, but it is less self-explanatory and can be harder to maintain than XLOOKUP. Assume names are in A2:A100, return values are in B2:B100, and the search term is in E2:

With names in A2:A100, return values in B2:B100, and the search term in E2, use: =LOOKUP(2,1/(A2:A100=E2),B2:B100)

The comparison creates 1 for matching rows and errors for nonmatching rows. LOOKUP ignores those errors and, with this construction, returns the value associated with the last match. If there is no match, the formula returns an error rather than the “Not found” text in the XLOOKUP example. You can wrap it in IFNA if you want a custom message, but test the result with your actual data before relying on a legacy formula.

Another option is to sort or filter the data so the desired record is easy to see. That can be sensible for a one-time check, but changing sort order may affect other users or formulas. A lookup formula is usually better when the result needs to update automatically as records are added.

Last entry is not always the latest record

Reverse lookup is useful for finding the last matching row, but it does not evaluate which record has the newest date. Consider a table where a customer’s January transaction is on row 20 and a December transaction is on row 30. If the table was sorted by customer name or entered in a different sequence, searching upward from row 30 may not return the latest transaction by date.

If “latest” means the greatest date, first make sure the dates are represented as real Excel dates, then choose a method that compares dates rather than relying on row order. One practical approach is to sort the table by date from oldest to newest and use a reverse lookup on the customer column; the last matching row will then have the latest date, provided the relevant records are included and the sort is consistent. Alternatively, use a formula designed to calculate the maximum date for that customer and retrieve the associated record.

Also decide how to handle ties. Two transactions can share the same date, and a date-only field does not establish which one occurred later. If the distinction matters, include a time or a unique transaction ID in the data and define which record should count as last.

Common errors and troubleshooting

  • #N/A: The lookup value may not be present, or values that look identical may differ. Check for leading or trailing spaces, number-versus-text mismatches, and the range boundaries.
  • Unexpected result: Confirm that -1 is in the search_mode argument of XMATCH or XLOOKUP, not in MATCH’s match_type argument. Also check that the lookup range covers the rows you intend to search.
  • Wrong related value: Make sure the lookup and return ranges have matching dimensions and start on corresponding rows.
  • Formula name not recognized: Your Excel version may not support XMATCH or XLOOKUP. Use a compatible alternative or open the workbook in Microsoft 365 or Excel 2021 and later.
  • Formula appears as text: The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, then re-enter the formula.

When matching text, Excel’s ordinary exact lookup is generally not case-sensitive. Thus “mira” and “Mira” can be treated as the same lookup value. If capitalization must distinguish records, a case-sensitive formula requires a different approach; simply changing the search direction does not make XMATCH or XLOOKUP case-sensitive.

Frequently Asked Questions

Can MATCH search from bottom to top?
No. MATCH has no search-direction argument. Use XMATCH with search_mode set to -1 to find the last occurrence’s position, or use XLOOKUP with search_mode set to -1 to return a related value.

What does -1 mean in XMATCH?
In XMATCH’s fourth argument, -1 means search last to first. In the third argument, -1 means an exact match or the next smaller item, depending on the lookup value and available data. To request an ordinary exact match, use 0 as the third argument.

Does reverse lookup return the newest entry?
Only if the records are ordered so the newest one is last within the lookup range. A reverse search finds the last matching row in the range’s current order; it does not inspect dates to determine which is newest.

Can I search a row from right to left?
Yes. XMATCH and XLOOKUP can search a horizontal range from its final cell toward its first when search_mode is -1. Use a return range laid out in the same direction and with the same number of cells.

What if I have an older Excel version?
Older versions do not include XMATCH or XLOOKUP. The LOOKUP construction shown above can return the last matching value, while MATCH itself returns the first exact match. Check compatibility and test legacy formulas in a copy of the workbook, especially when ranges include errors or unusual data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.