If the value is found then the result is TRUE otherwise the result is FALSE.So this will count the value if it appears anywhere in the range. We concatenated the asterisk before and after the cell reference D4 so now it will be counted as a substring. Here we used the Asterisk (*) as a Wildcard for one or more characters. Notice that any row that contains Mavs, Spurs, or Rockets receives a value of Texas while all other rows receive a value of Not Texas. The following screenshot shows how to use this formula in practice: Notice that any row that contains Mavs, Spurs, or Rockets receives a value of Yes while all other rows receive a value of No.Īlso note that we could return values other than “Yes” and “No.”įor example, we could use the following formula to return “Texas” or “Not Texas” instead: =IF(OR(COUNTIF( A2,"*"& $E$2:$E$4&"*")), "Texas", "Not Texas") We can type this formula into cell C2 and then copy and paste it down to the remaining cells in column C: Then we’ll use the following formula to check if the value in the Team column contains any of the text values in column E: =IF(OR(COUNTIF( A2,"*"& $E$2:$E$4&"*")), "Yes", "No") Suppose we’d like to create a new column that tells us whether each team is from Texas or not.įirst, we’ll create a list of the Texas teams in column E: There are three teams in the list from Texas: Mavs, Spurs, and Rockets. Suppose we have the following dataset in Excel that shows the number of points scored by various basketball players: Example: Check if Cell Contains Text from List in Excel
The following example shows how to use this formula in practice. In this example, if cell A1 contains any of the text values in the range E2:E8 then it will return a Yes, otherwise it will return a No.
You can use the following formula in Excel to check if a cell contains text from a list: =IF(OR(COUNTIF( A1,"*"& $E$2:$E$8&"*")), "Yes", "No")
0 kommentar(er)
