29/04/2020 If cell contains text from list

If cell contains text from list

Excel > Functions > Logical > IF function > If cell contains text from list

Share Guardar
Author: Oscar
Cronquist Article last
updated on March 23, 2020

This article demonstrates several ways to check if a cell contains a value based on a
list. The first example shows how to check if any of the values in the list is in the cell.

The remaining examples show formulas that also return the matching values. You
may need different formulas based on the Excel version you are using.
https://www.get-digital-help.com/if-cell-contains-text-from-list/ 1/19
29/04/2020 If cell contains text from list

The image above shows an array formula in cell C3 that checks if cell B3 contains at
least one of the values in List (E3:E7), it returns "Yes" if any of the values are found
in column B and returns nothing if cell contains none of the values.

Example, cell B3 contains XBF which is found in cell E7. Cell B4 contains text ZDS
found in cell E6. Cell C5 contains no values in list.
=IF(OR(COUNTIF(B3,"*"&$E$3:$E$7&"*")), "Yes", "")
You need to enter this formula as an array formula if you are not an Excel 365
subscriber. There is another formula below that doesn't need to be entered as an
array formula, however, it is slightly larger and more complicated.

1. Type formula in cell C3.

2. Press and hold CTRL + SHIFT simultaneously.
3. Press Enter once.
4. Release all keys.

Excel adds curly brackets to the formula automatically if you successfully entered the
array formula. Don't enter the curly brackets yourself.

Explaining formula in cell C3

Step 1 - Check if cell contains any of the value in the list

The COUNTIF function lets you count cells based on a condition, however, it also
allows you to count cells based on multiple conditions if you use a cell range instead
of a cell.

COUNTIF(range, criteria)

The criteria argument utilizes a beginning and ending asterisk in order to match a
text string and not the entire cell value, asterisks are one of two wildcard characters
that you are allowed to use.

The ampersands concatenate the asterisks to cell range E3:E7.

COUNTIF(B3,"*"&$E$3:$E$7&"*")

becomes

COUNTIF("LNU, YNO, XBF", {"*MVN*"; "*QLL*"; "*BQX*"; "*ZDS*"; "*XBF*"})

and returns this array

{0; 0; 0; 0; 1}

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 2/19
29/04/2020 If cell contains text from list

which tells us that the last value in the list is found in cell B3.

Step 2 - Return TRUE if at least one value is 1

The OR function returns TRUE if at least one of the values in the array is TRUE, the
numerical equivalent to TRUE is 1.

OR({0; 0; 0; 0; 1})

returns TRUE.

Step 3 - Return Yes or nothing

The IF function then returns "Yes" if the logical test evaluates to TRUE and nothing if
the logical test returns FALSE.

IF(TRUE, "Yes", "")

returns "Yes" in cell B3.

Regular formula

The following formula is quite similar to the formula above except that it is a regular
formula and it has an additional INDEX function.
=IF(OR(INDEX(COUNTIF(B3,"*"&$E$3:$E$7&"*"),)), "Yes", "")
Download Excel file

IF-cell-contains-text-from-list.xlsx

Display matches if cell contains text from list

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 3/19
29/04/2020 If cell contains text from list

The image above demonstrates a formula that checks if a cell contains a value in the
list and then returns that value. If multiple values match then all matching values in
the list are displayed.

For example, cell B3 contains "ZDS, YNO, XBF" and cell range E3:E7 has two
values that match, "ZDS" and "XBF".

Formula in cell C3:

=TEXTJOIN(", ", TRUE, IF(COUNTIF(B3, "*"&$E$3:$E$7&"*"), $E$3:$E$7, ""))
The TEXTJOIN function is available for Office 2019 and Office 365 subscribers. You
will get a #NAME error if your Excel version is missing this function. Office 2019
users may need to enter this formula as an array formula.

The next formula works with most Excel versions.

Array formula in cell C3:

=INDEX($E$3:$E$7, MATCH(1, COUNTIF(B3, "*"&$E$3:$E$7&"*"), 0))
However, it only returns the first match. There is another formula below that returns
all matching values, check it out.

How to enter an array formula

Explaining formula in cell C3

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 4/19
29/04/2020 If cell contains text from list

Step 1 - Count cells containing text strings

The COUNTIF function lets you count cells based on a condition, we are going to
use multiple conditions. I am going to use asterisks to make the COUNTIF function
check for a partial match.

The asterisk is one of two wild card characters that you can use, it matches 0 (zero)
to any number of any characters.

COUNTIF(B3, "*"&$E$3:$E$7&"*")

becomes

COUNTIF("ZDS, YNO, XBF", {"*MVN*"; "*QLL*"; "*BQX*"; "*ZDS*"; "*XBF*"})

and returns {1; 0; 0; 0; 1}.

This array contains as many values as there values in the list, the position of each
value in the array matches the position of the value in the list. This means that we
can tell from the array that the first value and the last value is found in cell B3.

Step 2 - Return the actual value

The IF function returns one value if the logical test is TRUE and another value if the
logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

This allows us to create an array containing values that exists in cell B3.

IF(COUNTIF(B3, "*"&$E$3:$E$7&"*"), $E$3:$E$7, "")

becomes

IF(COUNTIF("ZDS, YNO, XBF", {"*MVN*"; "*QLL*"; "*BQX*"; "*ZDS*"; "*XBF*"}),

{"*MVN*"; "*QLL*"; "*BQX*"; "*ZDS*"; "*XBF*"}, "")

and returns {"";"";"";"ZDS";"XBF"}.

Step 3 - Concatenate values in array

The TEXTJOIN function allows you to combine text strings from multiple cell ranges
and also use delimiting characters if you want.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 5/19
29/04/2020 If cell contains text from list

TEXTJOIN(", ", TRUE, IF(COUNTIF(B3, "*"&$E$3:$E$7&"*"), $E$3:$E$7, ""))

becomes

TEXTJOIN(", ", TRUE, {"";"";"";"ZDS";"XBF"})

and returns text strings ZDS, XBF.

Previous Excel versions

The image above demonstrates a formula that returns multiple matches if the cell
contains values from a list. This array formula works with most Excel versions.

Array formula in cell C3:

=IFERROR(INDEX($G$3:$G$7, SMALL(IF(COUNTIF($B3, "*"&$G$3:$G$7&"*"),
MATCH(ROW($G$3:$G$7), ROW($G$3:$G$7)), ""), COLUMNS($A$1:A1))), "")
How to enter an array formula

Copy cell C3 and paste to cell range C3:E15.

Explaining formula in cell C3

Step 1 - Identify matching values in cell

The COUNTIF function lets you count cells based on a condition, we are going to
use a cell range instead. This will return an array of values.
https://www.get-digital-help.com/if-cell-contains-text-from-list/ 6/19
29/04/2020 If cell contains text from list

COUNTIF($B3, "*"&$G$3:$G$7&"*")

becomes

COUNTIF("ZDS, YNO, XBF", {"*MVN*"; "*QLL*"; "*BQX*"; "*ZDS*"; "*XBF*"})

and returns {0; 0; 0; 1; 1}.

Step 2 - Calculate relative positions of matching values

The IF function returns one value if the logical test is TRUE and another value if the
logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

This allows us to create an array containing values representing row numbers.

IF(COUNTIF($B3, "*"&$G$3:$G$7&"*"), MATCH(ROW($G$3:$G$7),

ROW($G$3:$G$7)), "")

becomes

IF({0; 0; 0; 2; 1}, MATCH(ROW($G$3:$G$7), ROW($G$3:$G$7)), "")

becomes

IF({0; 0; 0; 2; 1}, {1; 2; 3; 4; 5}, "")

and returns {""; ""; ""; 4; 5}.

Step 3 - Extract the k-th smallest number

I am going to use the SMALL function to be able to extract one value in each cell in
the next step.

SMALL(array, k)

SMALL(IF(COUNTIF($B3, "*"&$G$3:$G$7&"*"), MATCH(ROW($G$3:$G$7),

ROW($G$3:$G$7)), ""), COLUMNS($A$1:A1)))

becomes

SMALL({""; ""; ""; 4; 5}, COLUMNS($A$1:A1)))

The COLUMNS function calculates the number of columns in a cell range, however,
the cell reference in our formula grows when you copy the cell and paste to adjacent

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 7/19
29/04/2020 If cell contains text from list

cells to the right.

SMALL({0; 0; 0; 4; 5}, COLUMNS($A$1:A1)))

becomes

SMALL({""; ""; ""; 4; 5}, 1)

and returns 4.

Step 4 - Return value based on row number

The INDEX function returns a value from a cell range or array, you specify which
value based on a row and column number. Both
the [row_num] and [column_num] are optional.

INDEX(array, [row_num], [column_num])

INDEX($G$3:$G$7, SMALL(IF(COUNTIF($B3, "*"&$G$3:$G$7&"*"),

MATCH(ROW($G$3:$G$7), ROW($G$3:$G$7)), ""), COLUMNS($A$1:A1)))

becomes

INDEX($G$3:$G$7, 4)

becomes

INDEX({"MVN";"QLL";"BQX";"ZDS";"XBF"}, 4)

and returns "ZDS" in cell C3.

Step 5 - Remove error values

The IFERROR function lets you catch most errors in Excel formulas except #SPILL!
errors. Be careful when using the IFERROR function, it may make it much harder
spotting formula errors.

IFERROR(value, value_if_error)

There are two arguments in the IFERROR function. The value argument is returned
if it is not evaluating to an error. The value_if_error argument is returned if
the value argument returns an error.

IFERROR(INDEX($G$3:$G$7, SMALL(IF(COUNTIF($B3, "*"&$G$3:$G$7&"*"),

MATCH(ROW($G$3:$G$7), ROW($G$3:$G$7)), ""), COLUMNS($A$1:A1))), "")

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 8/19
29/04/2020 If cell contains text from list

Related articles

Nested IF functions

Nested IF statements in a formula are multiple combined IF functions so more

conditions and outcomes become possible. They all are […]

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 9/19
29/04/2020 If cell contains text from list

IF with AND function – multiple conditions

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 10/19
29/04/2020 If cell contains text from list

The AND function allows you to have multiple conditions in an IF function, you can
have up to 254 arguments. […]

Use IF + COUNTIF to perform numerous conditions

The COUNTIF function allows you to construct a small IF formula that carries out
plenty of logical expressions. Combining the IF […]

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 11/19
29/04/2020 If cell contains text from list

If cell contains text

The easiest way to check if a cell contains a specific text string is, in my opinion, the
IF and […]

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 12/19
29/04/2020 If cell contains text from list

If cell equals value from list

Regular formula Formula in cell C3: =IF(COUNTIF($E$3:$E$5,B3),"Yes","No") The

COUNTIF function counts how many values in E3:E5 match cell B3, it returns […]

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 13/19
29/04/2020 If cell contains text from list

How to use the IF function

Checks if a logical expression is met. Returns a specific value if TRUE and another
specific value if FALSE.

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 14/19
29/04/2020 If cell contains text from list

Nested IF functions

Nested IF statements in a formula are multiple combined IF functions so more

conditions and outcomes become possible. They all are […]

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 15/19
29/04/2020 If cell contains text from list

If cell contains multiple values

The array formula in cell C3 checks if text string in B3 contains all values in F2:F3.
Related articles

IF with AND function – multiple conditions

The AND function allows you to have multiple conditions in an IF function, you can
have up to 254 arguments. […]

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 16/19
29/04/2020 If cell contains text from list

If cell equals value from list

Regular formula Formula in cell C3: =IF(COUNTIF($E$3:$E$5,B3),"Yes","No") The

COUNTIF function counts how many values in E3:E5 match cell B3, it returns […]

Comments (6)
https://www.get-digital-help.com/if-cell-contains-text-from-list/ 17/19
29/04/2020 If cell contains text from list

6 Responses to “If cell contains text from list”

. Ricardo Puentes says:

March 2, 2020 at 5:36 am
Great post, very helpful. thanks.
Reply
. Ty Webb says:
March 19, 2020 at 2:33 pm
How would you show the actual match rather than just "Yes"?
Reply
Oscar says:
March 19, 2020 at 6:08 pm
Ty Webb

Great question.

Array formula in cell C3:

=TEXTJOIN(", ", TRUE, IF(COUNTIF(B3, "*"&$E$3:$E$7&"*"), $E$3:$E$7, ""))

. kayley says:
March 21, 2020 at 12:29 pm
The post is helpful!

I tried the array formula to return the actual match but it didn't work for me. I selected
C3:C15, copied the formula in the comment section and pressed ctrl+shift+enter but
all the cells reflected the array formula {=TEXTJOIN(", ", TRUE, IF(COUNTIF(B3,
"*"&$E$3:$E$7&"*"), $E$3:$E$7, ""))}.

May I know if I did something wrong?

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 18/19
29/04/2020 If cell contains text from list

Reply
Oscar says:
March 23, 2020 at 8:49 am
kayley

Did you enter the curly brackets? If you did, don't. They appear automatically to
confirm that you successfully entered an array formula.

. Philip says:
April 24, 2020 at 1:42 am
Thanks alot for posting this. It has really sorted me out. I will shine like a super star. I
owe you tons! be blessed!
Reply

Leave a Reply

Name (required)

Mail (will not be

published) (required)

Website

Notify me of followup comments via e-mail

How to comment

How to add a formula to your comment

<code>Insert your formula here.</code>

Convert less than and larger than signs

https://www.get-digital-help.com/if-cell-contains-text-from-list/ 19/19