ThisCalculator

Everyday

Last Match Cell Contains? Excel Lookup Guide

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This article explains how to find the last cell in an Excel range that contains a specific text or value, which is a common lookup problem. You will learn formulas using LOOKUP, INDEX/MATCH, and XLOOKUP that return the last matching entry, along with practical examples and troubleshooting tips. The methods work in Excel 365, Excel 2021, and older versions, with variations noted for each.

Formula

The core formula for finding the last cell that contains a specific text (as a substring) in a range is:

=LOOKUP(2, 1/ISNUMBER(SEARCH(target, range)), range)

For an exact match (whole cell equals the target), use:

=LOOKUP(2, 1/(range=target), range)

Where:

  • target – the text you are searching for (can be a cell reference).
  • range – the vertical or horizontal range to search (e.g., A1:A10).
  • SEARCH – finds the position of the target within each cell (case-insensitive). Use FIND for case-sensitive.
  • ISNUMBER – converts the search result to TRUE/FALSE.
  • 1/… – turns TRUE into 1 and FALSE into #DIV/0! error.
  • LOOKUP(2, …) – finds the last value that is less than or equal to 2, ignoring errors, and returns the corresponding value from the range.

Worked Example

Suppose you have a list in A1:A10 and you want the last cell that contains “apple”. The default values in the calculator above use a range of 10 cells, but for a concrete example, let’s use actual data:

  1. Enter the following values in A1:A10: “apple pie”, “banana”, “apple cider”, “cherry”, “apple sauce”, “date”, “fig”, “apple turnover”, “grape”, “apple”
  2. Set target to “apple” (cell B1).
  3. The formula =LOOKUP(2, 1/ISNUMBER(SEARCH(B1, A1:A10)), A1:A10) evaluates as follows:
    • SEARCH returns numbers for cells containing “apple” (1, 5, 8, 10) and #VALUE! for others.
    • ISNUMBER converts those to TRUE, TRUE, TRUE, TRUE and FALSE for the rest.
    • 1/TRUE = 1, 1/FALSE = #DIV/0!.
    • LOOKUP(2, …) finds the last 1 (at position 10) and returns the corresponding value from A1:A10, which is “apple”.
  4. The result is “apple” – the last cell containing “apple”.

If you want the position instead of the value, use =LOOKUP(2, 1/ISNUMBER(SEARCH(B1, A1:A10)), ROW(A1:A10)) and adjust for the starting row.

Why the LOOKUP Trick Works

The LOOKUP function is designed to find a value in a vector. When you give it a search value of 2 and a vector of 1s and errors, it will always return the last 1 because it assumes the vector is sorted and finds the largest value that is less than or equal to 2. The errors are ignored. This is a classic Excel hack that works in all versions.

For exact matches, the simpler =LOOKUP(2, 1/(range=target), range) works the same way. The division by the boolean array (TRUE/FALSE) creates 1s and #DIV/0! errors.

Using INDEX/MATCH for More Flexibility

If you need to return a value from a different column (like a lookup table), use INDEX/MATCH:

=INDEX(return_range, MATCH(2, 1/ISNUMBER(SEARCH(target, search_range)), 1))

This is an array formula in older Excel (must be entered with Ctrl+Shift+Enter), but in Excel 365 it works normally. The MATCH with 1 as the last argument finds the largest value less than or equal to 2, which again is the last 1.

For exact matches, replace the SEARCH part with (search_range=target).

XLOOKUP: The Modern Solution

If you have Excel 365 or Excel 2021, XLOOKUP makes this much simpler:

=XLOOKUP(target, range, range, "not found", 2, -1)

  • The 5th argument (2) means wildcard match – the target can be a substring.
  • The 6th argument (-1) means search from last to first.

This returns the last cell that contains the target. For an exact match, use 0 as the 5th argument.

XLOOKUP is the recommended approach for modern Excel versions because it is clearer and less error-prone.

Common Mistakes and How to Avoid Them

  • Using VLOOKUP – VLOOKUP always returns the first match, not the last. Do not use it for this task.
  • Forgetting to press Ctrl+Shift+Enter – In Excel 2019 and earlier, the INDEX/MATCH array formula requires this. If you see a #VALUE! error, try re-entering with Ctrl+Shift+Enter.
  • Case sensitivity – SEARCH is case-insensitive; FIND is case-sensitive. Choose based on your need.
  • Whole vs. partial match – If you want the last cell that equals the target exactly, use = instead of SEARCH. If you want partial, use SEARCH.
  • Blank cells – The formula ignores blanks because they don’t contain the target. If you need to treat blanks as matches, adjust the condition.

Frequently Asked Questions

How do I get the last match in a column with multiple criteria?

Use the same LOOKUP trick but multiply the conditions: =LOOKUP(2, 1/((range1=crit1)*(range2=crit2)), return_range). The multiplication turns TRUE*TRUE into 1, and any FALSE makes it 0, which becomes #DIV/0! after division.

Why does my formula return #N/A?

This means no match was found. Check that the target text exists in the range, and that you are using the correct case sensitivity. Also, if using array formulas, ensure you entered them correctly.

Can I get the last match from a row instead of a column?

Yes, the formulas work horizontally as well. Just replace the column range with a row range (e.g., A1:J1). The LOOKUP function works with both orientations.

What if I want the last match in a filtered list?

These formulas ignore hidden rows. If you need to consider only visible cells, use the SUBTOTAL function with a helper column, or use the AGGREGATE function with option 7 (ignore hidden rows).

Is there a way to get the last match without an array formula?

Yes, the LOOKUP function itself does not require array entry, even in older Excel. The INDEX/MATCH version does. If you want to avoid arrays, stick with LOOKUP.

How do I return the cell address of the last match?

Use =ADDRESS(LOOKUP(2, 1/ISNUMBER(SEARCH(target, range)), ROW(range)), COLUMN(range)) for a single column. For a range that spans multiple columns, you’ll need a more complex formula.

Related

Formula used
in t | Target text | - | "apple" | 0
in d | Data range (A1:A10) | - | 10 | 0
out pos | Last match position | - | d | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.