Explanation

In this example, the goal is to test values in column B to see if they end with a specific text string, which is “jwb” in the worksheet shown. This problem can be solved with the RIGHT function, as explained below.

RIGHT function

The RIGHT function extracts a given number of characters from the right side of a text string. For example, the formula below returns the last three letters of “apple”, which is “ple”:

=RIGHT("apple",3) // returns "ple"

This means we can use the RIGHT function to test if cell B5 ends with “jwb” like this:

=RIGHT(B5,3)="jwb"

The RIGHT function extracts the last 3 characters in cell B5 and the result is compared to the string “jwb”, forcing a TRUE or FALSE result. The formula is solved like this:

=RIGHT(B5,3)="jwb"
=RIGHT("ABC-1224-HNN",3)="jwb"
="HNN"="jwb"
=FALSE

For cell B5 the result is FALSE, since “ABC-1224-HNN” does not end with “jwb”. In cell B6, however, the result is TRUE, since “XYZ-6543-JWB” does end with “jwb”.

=RIGHT(B6,3)="jwb"
=RIGHT("XYZ-6543-JWB",3)="jwb"
="XYZ"="jwb"
=TRUE

Note that Excel is not case-sensitive by default, so “JWB”=“jwb” will return TRUE in a formula. Also note the num_chars argument is set to 3 above because we want to work with the last 3 letters in the cell only. However, this value needs to be modified to suit the situation. For example, to test for a value that ends with “apple”, num_chars should be set to 5:

=RIGHT(B5,5)="apple"

Case-sensitive option

Excel is not case-sensitive by default, but you can easily adapt the formula to use the EXACT function to make the formula case-sensitive like this:

=EXACT(RIGHT(B5,3),"JWB")

EXACT takes two arguments, text1 and text2 . EXACT will only return TRUE when text1 and text2 are exactly the same, taking into account case. For example:

=EXACT("abc","ABC") // returns FALSE
=EXACT("abc","Abc") // returns FALSE
=EXACT("abc","abc") // returns TRUE

Turning back to cell B6 in the worksheet shown, the two formulas below return different results:

=EXACT(RIGHT(B6,3),"jwb")// returns FALSE
=EXACT(RIGHT(B6,3),"JWB") // returns TRUE

The first formula returns FALSE because the EXACT function is case-sensitive, so “JWB” does not equal “jwb”. The second formula returns TRUE because “JWB” does equal “JWB” taking into account case. Note that we don’t need the equal to operator (=) in this formula because EXACT performs a comparison automatically.

If cell ends with

To adapt the formulas above to “If cell ends with”, simply drop the formulas into the IF function as the logical test. For example, to return “Yes” when a cell ends with “jwb” and “No” when not, you can use a formula like this

=IF(RIGHT(B5,3)="jwb", "Yes", "No")

The case-sensitive version of the formula works the same way:

=IF(EXACT(RIGHT(B5,3),"JWB"), "Yes", "No")

Other functions

It is worth noting that Excel contains two other functions, the SEARCH function and the FIND function that are meant to look for a substring in a text value. They could be used to solve this problem, but they are more work to configure, so I don’t see any advantage to using them. You could however use the COUNTIF function with a wildcard to solve this problem like this:

=COUNTIF(B5,"*jwb")<>0

This works fine, but keep in mind that COUNTIF is in a group of eight *IFS functions that won’t accept an array for the range argument. This means you can’t use COUNTIF to test values in an array returned by another operation. I don’t like this limitation, so I avoid the *IFS functions when there is a good alternative.

Explanation

In this example, the goal is to return a TRUE or FALSE result for each value in column B, based on whether it appears in the range E5:E9, which has been named “things” for convenience.

Context

Imagine you have a list of values in the range B5:B16 and you want to check each value to see if it appears in another list of values in the range E5:E9, which has been named “things”. You might think you can use a formula like this:

=B5=things

However, because we are comparing B5 to the range E5:E9, which contains five values, the formula will return five results. With “green” in cell B5, the result will be an array like this:

{FALSE;FALSE;TRUE;FALSE;FALSE}

And if we copy the formula down, each value in column B will return five results. This idea isn’t going to work as-is. You could build a more complicated formula using nested IF statements to check for each value separately, but this is a tricky path that will take much longer and greatly increase the chance of errors. A much simpler, cleaner approach is to use an array formula based on the SUMPRODUCT function.

Solution with SUMPRODUCT

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is a versatile function that can be used to solve many tricky problems in Excel. It works nicely in this case because it can handle arrays natively in any version of Excel.

Note: In Excel 2021 or Excel 365, you can use the SUM function instead of SUMPRODUCT and it will just work fine. However, if the worksheet is opened in an older version of Excel, it will appear as a traditional array formula surrounded by curly braces. If you need backward compatibility, SUMPRODUCT avoids this complication and works the same in all versions.

Back to the problem. As mentioned above, if we compare the value in cell B5 directly with the values in things (E5:E9), we get back an array that contains five TRUE or FALSE values. This seems inconvenient because it isn’t obvious how we can resolve this list of five results into a single TRUE or FALSE value. However, we can use SUMPRODUCT to process the array of TRUE/FALSE values and return a single result with a formula like this:

=SUMPRODUCT(--(B5=things))>0

At a high level, this formula counts the TRUE values in the array and then checks if the result is greater than zero. Working from the inside out, the expression B5=things returns five values, as explained above.

​=B5=things // returns {FALSE;FALSE;TRUE;FALSE;FALSE}

If we simplify the formula, we now have:

=SUMPRODUCT(--({FALSE;FALSE;TRUE;FALSE;FALSE}))>0

You might wonder what the double negative (–) is doing here. SUMPRODUCT is designed to process numeric values, and it will simply ignore TRUE and FALSE values. If we try to use the raw values, SUMPRODUCT will return zero:

=SUMPRODUCT(FALSE;FALSE;TRUE;FALSE;FALSE}) // returns 0

The double negative (–) is a simple trick to force Excel to convert the TRUE and FALSE values to their numeric equivalents, 1 and 0. After this operation is evaluated, we can simplify the formula to this:

=SUMPRODUCT({0;0;1;0;0})>0

Notice that each FALSE value in the array is now zero, and the lone TRUE in the array is now 1. Now you can see where we are headed. Since the array contains only numbers, SUMPRODUCT will return a sum. If we get any non-zero result, we have a match, so we use >0 to force a final result of either TRUE or FALSE:

=SUMPRODUCT({0;0;1;0;0})>0
=1>0
=TRUE

With a hard-coded list

There’s no requirement to use a range for your list of things to check for. If you’re only looking for a small number of things, you can use a list in array format, which is called an array constant . For example, if you’re just looking for the colors red, blue, and green, you can use {“red”,“blue”,“green”} like this:

=SUMPRODUCT(--(B5={"red","blue","green"}))>0

Using a range is a more flexible approach since the values appear on the worksheet and can be changed at any time.

Dealing with extra spaces

If the cells you are testing contain any extra space, they won’t match properly. To strip all extra space, you can modify the formula to use the TRIM function like so:

=SUMPRODUCT(--(TRIM(A1)=things))>0

TRIM will strip leading or trailing space characters from the value before it is compared to the list of things.