To look up the last matching value in Excel, search a list from the bottom upward or use a formula that returns the final matching row.
The right method depends on your Excel version, whether you need an exact match, and how you want to handle missing results.
Sample data for a last-match lookup
Suppose cells A2:B7 contain transaction records, with a customer in column A and an order total in column B:
- A2: Lee; B2: 12
- A3: Kim; B3: 18
- A4: Lee; B4: 25
- A5: Ana; B5: 9
- A6: Kim; B6: 31
- A7: Lee; B7: 42
If E2 contains Lee, the last matching total is 42, from row 7. “Last” means the lowest matching row in the range, not necessarily the most recent date.
If records are not sorted chronologically, sort them or use a date-based method when recency matters.
Use XLOOKUP to return the last match
In Microsoft 365 and Excel 2021 or later, XLOOKUP can search from the last row toward the first.
Use this formula to return the matching value from column B:
=XLOOKUP(E2,A2:A7,B2:B7,"Not found",0,-1)
The arguments specify the lookup value, lookup range, return range, message for a missing match, exact-match mode, and reverse search order.
The final -1 tells XLOOKUP to search from last to first.
With E2 set to Lee, the formula returns 42.
To return the last matching name itself, use the same range for both lookup and return:
=XLOOKUP(E2,A2:A7,A2:A7,"Not found",0,-1)
XLOOKUP is usually the clearest choice when available.
It supports exact matching, separate lookup and return ranges, and a custom not-found result.
The lookup and return ranges should have the same height or width.
Find the last matching row with LOOKUP
For older Excel versions, a common option is LOOKUP with a condition that produces a match indicator for each row:
=LOOKUP(2,1/(A2:A7=E2),B2:B7)
The condition A2:A7=E2 checks each name against E2.
Dividing 1 by that array creates 1 for matches and errors for nonmatches; LOOKUP searches for 2, which is larger than the match indicators, and returns the corresponding value from the last matching row.
For Lee, the result is 42.
This formula is a traditional array-style technique.
In many Excel editions, enter it normally with Enter; it does not typically require Ctrl+Shift+Enter.
Because it relies on LOOKUP's behavior with errors and approximate searching, test it carefully if adapting it to a more complex range or calculation.
Use INDEX and MATCH for a compatible alternative
INDEX and MATCH can also return the value from the final matching row.
This version works in older Excel releases that do not have XLOOKUP:
=INDEX(B2:B7,MATCH(2,1/(A2:A7=E2)))
MATCH searches for 2 in the array of 1s and errors, locating the last matching 1; INDEX returns the value at that position in B2:B7.
In Excel versions with dynamic arrays, Enter is sufficient.
In some legacy versions, this is an array formula and may require Ctrl+Shift+Enter.
If your version does not evaluate it as expected, use the LOOKUP formula or the helper-column method below.
Return the last matching value without array calculations
A helper column makes the matching logic visible and can be easier to troubleshoot.
In C2, enter this formula and fill it down through C7:
=IF(A2=$E$2,ROW(),0)
It records the worksheet row number for matches and 0 otherwise.
Then return the value from the largest recorded row:
=IF(MAX(C2:C7)=0,"Not found",INDEX(B:B,MAX(C2:C7)))
For Lee, the helper cells for rows 2, 4, and 7 contain 2, 4, and 7; the result is 42.
This approach is useful when you want to inspect which records matched.
Ensure the helper range contains only the rows relevant to the lookup, since unrelated positive row numbers could affect the maximum.
Handle missing matches and blank results
XLOOKUP can display a message directly when there is no match, as in =XLOOKUP(E2,A2:A7,B2:B7,"Not found",0,-1).
For LOOKUP or INDEX and MATCH, wrap the formula in IFERROR:
=IFERROR(LOOKUP(2,1/(A2:A7=E2),B2:B7),"Not found")
A blank in the return column can appear as 0 in some lookup formulas.
If a matching row may legitimately have an empty return cell, decide whether you want to show 0, an empty string, or a message.
For example, return the matching row number first and then check that cell explicitly; this distinguishes a missing match from a match whose result is blank.
Common edge cases to check
- Exact versus partial matching: The XLOOKUP example uses exact matching with
0.
It does not find a name embedded inside a longer text string.
For a partial search, use wildcards deliberately and account for how they treat characters such as*and?. - Extra spaces: A value such as
Leemay not matchLee.
Clean source data with TRIM or standardize entries before lookup. - Numbers stored as text: The number 42 and text
42may behave differently in comparisons.
Convert values consistently if a numeric lookup unexpectedly fails. - Duplicates: These formulas return the last match within the specified range.
If the range starts at row 2 and ends at row 7, they cannot return a match outside those rows. - Growing datasets: Fixed ranges such as A2:A7 need updating as records are added.
Excel Tables and structured references can expand automatically, though the exact formula syntax depends on your table and column names. - Performance: Avoid full-column array calculations over very large sheets when a bounded range or Table column will do.
Choose a formula by Excel version
Use XLOOKUP with reverse search mode -1 in Microsoft 365 or Excel 2021 and later.
Use LOOKUP(2,1/(condition),return_range) for a compact option in many older versions, or INDEX and MATCH when you need a familiar lookup structure.
A helper column is a practical alternative when array formulas are difficult to maintain.
Before using a result in a report, confirm that the lookup range covers all records and that “last row” matches what you mean by “latest.” If dates determine recency, sort the data consistently or build the lookup around the date field so the returned row reflects the intended record.