Get last match cell contains

The goal is to search through a cell for one of several specified values and return the last match found when one exists. The worksheet includes a list of colors in the range E5:E11 (which is named list) and a series of short sentences in the range B5:B16. The task is to add a formula in column C that will search through each sentence in B5:B16 and extract the last color list (E5:E11) that appears in each sentence. One way to solve this problem is with a formula that utilizes the ISNUMBER, SEARCH, and XLOOKUP functions. In older versions of Excel, you can use an alternative formula, as explained below.

Key functions

Here are the functions used in the XLOOKUP formula seen in the worksheet:

1. The ISNUMBER function checks if a given input or expression results in a number. It returns TRUE if the value is numeric, and FALSE if not.

2. The SEARCH function is used to find the starting position of a specific substring within a string. The syntax is SEARCH(find_text, within_text, [start_num]). If the substring (find_text) is found, SEARCH returns the starting position as a number. If not, it results in an error.

3. The XLOOKUP function is a modern upgrade to the older VLOOKUP function. XLOOKUP allows you to search for a key value in a specified range or array, and return a corresponding value from another range or array. The syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).

How the formula works

The formula in cell C5 is as follows:

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

Working from the inside out, the SEARCH function looks for each color in the named range list (E5:E11) in cell B5:

SEARCH(list,B5)

If a color is found, SEARCH returns its starting position as a number. If a color is not found, SEARCH returns an #VALUE! error. Because E5:E11 contains 7 colors, SEARCH returns 7 results in an array like this:

{#VALUE!;34;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5}

Notice the second value is 34 and the seventh value is 5. These numbers indicate the numeric positions of the colors "white" (5) and "blue" (34) in cell B5. The #VALUE! errors indicate colors in E5:E11 that were not found. To convert these values into something more useful, we use the ISNUMBER function:

ISNUMBER(SEARCH(list,B5))

This function is an error handler. Valid numbers become TRUE, and errors become FALSE. The result from ISNUMBER is an array with 7 values like this:

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}

Note we have a TRUE value in the 2nd position ("blue") and the 7th position ("white"). All other colors in the list were not found. Next, we have the XLOOKUP function, which is the main driver of the formula:

=XLOOKUP(TRUE,ISNUMBER(SEARCH(list,B5)),list,"na")

Notice the lookup_value is set to TRUE. After SEARCH and ISNUMBER are evaluated, we have the following:

=XLOOKUP(TRUE,{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},list,,,-1)

The return_array is provided as list (E5:E11), and search_mode is set to -1 to search last to first. If_not_found and match_mode are left empty. XLOOKUP scans the array starting at the end. When a match is found, XLOOKUP returns the corresponding color from list (E5:E11). Since the last value is TRUE, XLOOKUP returns the seventh color in E5:E11, which is "white". If no match is found, XLOOKUP returns an #N/A error. To customize this result, provide a value for if_not_found.

Last match in the sentence

The language used in this example is somewhat ambiguous because it is not clear whether we are referring to the "last color found in the list of colors" or the "last color found in each sentence". These are two distinctly different operations and therefore need two different formulas. The formula above returns the last match found in list (E5:E11). To find the last matched color in a sentence (ignoring the order of colors E5:E11) you can use a different formula like this:

=XLOOKUP(LEN(B5),SEARCH(list,B5),list,,-1)

Here, we use the LEN function to create the lookup_value. This is the total number of characters in B5, which is a number that XLOOKUP will never find. The SEARCH function is used exactly the same way as before. The return_array is list (B5:B11), if_not_found is omitted, and match_mode is set to -1 for "exact match or next smallest" behavior.

After LEN and SEARCH are evaluated, we have:

=XLOOKUP(42,{#VALUE!;34;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5},list,,-1)

The value of 42 is deliberately larger than any number returned by SEARCH. XLOOKUP then matches the next smallest number (34) and returns the 2nd color ("blue") as a final result.

For more details on XLOOKUP, see How to use the XLOOKUP function.

Legacy Excel

In Excel 2019 and older, the XLOOKUP function is not available, but you can use a formula based on the LOOKUP function to get the last match found in list (E5:E11). LOOKUP can work well in "last match" scenarios, because it can handle array operations in older versions of Excel natively, without special handling. We can use LOOKUP like this:

=LOOKUP(2,1/ISNUMBER(SEARCH(list,B5)),list)

This formula finds the last color matched in list (E5:E11), which is "white". Working from the inside out, we use SEARCH + ISNUMBER as explained earlier:

ISNUMBER(SEARCH(list,B5))

The SEARCH function tries to find each of the 7 colors in list (E5:E11). Because E5:E11 contains 7 colors, SEARCH returns 7 results in an array like this:

ISNUMBER({#VALUE!;34;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5})

Notice the 2nd value is 34 and the 7th value is 5. These numbers indicate the numeric positions of the colors "white" (5) and "blue" (34) in cell B5. The #VALUE! errors indicate other colors not found. SEARCH returns these results to ISNUMBER, and ISNUMBER returns an array of TRUE and FALSE values:

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}

Next, we divide the number 1 by this array. During division, the math operation coerces TRUE to 1 and FALSE to zero, so you should visualize the operation like this:

1/{0;1;0;0;0;0;1}

When the actual division takes place, 1 divided by 1 is 1, and 1 divided by 0 creates a #DIV/0 error:

{#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

This array is the lookup_vector inside LOOKUP, so at this point, we have:

=LOOKUP(2,{#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1},list)

Notice that the lookup value is 2. This may seem odd, but it is intentional. We use 2 as a lookup value to force LOOKUP to scan to the end of the data. LOOKUP will automatically ignore errors, so the only thing left to match are the 1s. It will scan through the 1s looking for a 2 that can never be found. When it reaches the end of the array, it will "step back" to the last valid value (the last 1), and return the corresponding color from list (E5:E11), which is "white".

Last match in the sentence

To get the last match found in the sentence in Legacy Excel, you can use a formula based on INDEX and MATCH:

=INDEX(list,MATCH(AGGREGATE(14,6,SEARCH(list,B5),1),SEARCH(list,B5),0))

Note this is an array formula that must be entered with control + shift + enter in Excel 2019 and earlier.

The main trick is the lookup_value in MATCH, which is calculated with the AGGREGATE function like this:

AGGREGATE(14,SEARCH(list,B5),1) // get max value

Here, we use AGGREGATE to get the maximum value in the results returned by SEARCH. The number 14 indicates that AGGREGATE should apply the behavior of the LARGE function, which is designed to extract nth largest values. We use AGGREGATE because the array will contain errors (returned by SEARCH when colors aren't found), and we need to ignore these errors and still get the maximum numeric value. AGGREGATE works well here because it has the option to ignore errors. The result from AGGREGATE is 34, which is returned to MATCH as the lookup_value. The lookup_array is created by the SEARCH function:

{#VALUE!;34;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5}

We don't use ISNUMBER in this case because we need to be able to find the number calculated by AGGREGATE. Back in the formula, we now have:

=INDEX(list,MATCH(34,{#VALUE!;34;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5},0))

The result from MATCH is 2, because 34 is the 2nd value in the lookup_array. MATCH returns this number to INDEX as the row_num argument:

=INDEX(list,7) // returns "white"

The final result is "blue", the last color found in the sentence.

For more details on INDEX with MATCH, see How to use the INDEX and MATCH.