Get last match cell contains

How to get the last match where a cell contains

You can use the XLOOKUP, TRUE, ISNUMBER, and SEARCH functions to get the last match in a range where the cell contains a matching value.

How to get the last match where a cell contains

  1. Open Excel
  2. Type =XLOOKUP(TRUE,ISNUMBER(SEARCH(list,B3)),list,,,-1)
  3. Press Enter
  4. The formula returns the last match from the list that meets the criteria.

Example

In the example, you want to find the last match from the list that appears in the text in B3 and return that match in column C. You can use the XLOOKUP formula with some adjustments to retrieve the last cell in a range that contains a specific value or meets a condition (like a partial match). As usual, we are using named ranges to simplify the formula. Select range E3:E10 and add a name to the range, in the example, “list“.

Formula:

=XLOOKUP(TRUE,ISNUMBER(SEARCH(list,B3)),list,,,-1)

How to get the last match where a cell contains

This formula will return the contents of the last matching cell in B3:B10 that contains any items in the list.

Explanation

Let’s take a closer look at the formula and evaluate it from the inside out.

SEARCH(list, B3): SEARCH looks for each item in the list within cell B3. The “list” is a named range, which contains keywords like “lavender,” “red,” “gray,” etc., located in column E. SEARCH will search each word in the list within the text in cell B3. It returns the position number where each keyword is found or an error (#VALUE!) if the keyword is not present.

For example, if B3 contains “The yellow sun and lavender clouds shone bright in the sky.” and “list” includes “lavender” and “yellow,” SEARCH(list, B3) would output an array of positions like {25, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 5} for each item in list.

ISNUMBER(SEARCH(list, B3)):

ISNUMBER checks whether each value returned by SEARCH is a number (indicating a match). This converts the result from SEARCH into an array of TRUE and FALSE values, with TRUE indicating a match and FALSE indicating no match.

ISNUMBER checks whether each value returned by SEARCH

Using the previous example, ISNUMBER(SEARCH(list, B3)) would give {TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE}.

XLOOKUP(TRUE, ISNUMBER(SEARCH(list, B3)), list, , , -1):

XLOOKUP searches the array from ISNUMBER(SEARCH(list, B3)) for the value TRUE, which means it’s looking for the last occurrence of a TRUE (indicating the last match) in the array. The -1 in the last argument tells XLOOKUP to search from the end of the array backward, making it return the last item in the list that matches any text found in B3. When TRUE is found, XLOOKUP returns the corresponding item from the list.

For instance, if the last TRUE corresponds to “yellow,” XLOOKUP will return “yellow” in C3.

How to get the last match where a cell contains

Download the practice file.