The OR function in Excel is a logical function that returns TRUE if any of the arguments are TRUE, and FALSE if all of the arguments are FALSE. The OR function can be used to check cells for text by comparing them with a text value or an array of text values.
The syntax of the OR function is:
=OR(logical1, [logical2], ...)
logical1is the first condition to test. It can be a logical expression, a cell reference, a range, or another function that returns a logical value (TRUE or FALSE).logical2, … are optional additional conditions to test, up to 255. They can be the same aslogical1.
The OR function evaluates each argument in order, and returns TRUE as soon as it finds one that is TRUE. If none of the arguments are TRUE, it returns FALSE.
Procedures
To use the OR function to check cells for text, follow these steps:
- Enter the text value or the array of text values that you want to check against in a cell or a range of cells. For example, if you want to check if a cell contains “Yes” or “No”, enter {“Yes”,“No”} in cell A1.
- Select the cell or the range of cells that you want to check for text. For example, if you want to check cells B2:B10, select them.
- Enter the OR function in the formula bar, and use the cell or the range of cells that contain the text value or the array of text values as one of the arguments. For example, enter =OR(B2:B10=A1) in the formula bar.
- Press Enter or Ctrl+Enter to apply the formula to the selected cells. The formula will return TRUE if any of the cells in B2:B10 match the text value or the array of text values in A1, and FALSE otherwise.
Explanation
The OR function can be used to check cells for text by comparing them with a text value or an array of text values. This can be useful for filtering, highlighting, or counting cells that meet certain criteria based on text.
For example, suppose you have a list of products and their categories in columns A and B, and you want to check which products belong to either “Books” or “Games” category. You can use the OR function to do this by following these steps:
- Enter the text values that you want to check against in a cell or a range of cells. In this case, enter {“Books”,“Games”} in cell C1.
- Select the cells that you want to check for text. In this case, select cells B2:B10.
- Enter the OR function in the formula bar, and use the cell or the range of cells that contain the text values as one of the arguments. In this case, enter =OR(B2:B10=C1) in the formula bar.
- Press Enter or Ctrl+Enter to apply the formula to the selected cells. The formula will return TRUE if any of the cells in B2:B10 match either “Books” or “Games”, and FALSE otherwise.
Table
| Product | Category | OR Function |
|---|---|---|
| Harry Potter | Books | TRUE |
| Monopoly | Games | TRUE |
| iPhone | Electronics | FALSE |
| The Hunger Games | Books | TRUE |
| Chess | Games | TRUE |
| Laptop | Electronics | FALSE |
| The Lord of the Rings | Books | TRUE |
| Scrabble | Games | TRUE |
| Camera | Electronics | FALSE |
Example
To illustrate how to use the OR function to check cells for text in Excel, let’s consider a scenario where you have a list of students and their grades in columns A and B, and you want to check which students have either an “A” or a “B” grade. You can use the OR function to do this by following these steps:
- Enter the text values that you want to check against in a cell or a range of cells. In this case, enter {“A”,“B”} in cell C1.
- Select the cells that you want to check for text. In this case, select cells B2:B10.
- Enter the OR function in the formula bar, and use the cell or the range of cells that contain the text values as one of the arguments. In this case, enter =OR(B2:B10=C1) in the formula bar.
- Press Enter or Ctrl+Enter to apply the formula to the selected cells. The formula will return TRUE if any of the cells in B2:B10 match either “A” or “B”, and FALSE otherwise.
| Student | Grade | OR Function |
|---|---|---|
| Alice | A | TRUE |
| Bob | C | FALSE |
| Charlie | B | TRUE |
| David | D | FALSE |
| Eve | A | TRUE |
| Frank | B | TRUE |
| Grace | C | FALSE |
| Harry | A | TRUE |
| Ivy | B | TRUE |
The result of the scenario is that 6 out of 9 students have either an “A” or a “B” grade, as indicated by the TRUE values in column C.
Other Approaches
Besides using the OR function, there are other ways to check cells for text in Excel. Some of them are:
- Using the COUNTIF or COUNTIFS function to count the number of cells that match a text value or an array of text values. For example, =COUNTIF(B2:B10,C1) or =COUNTIFS(B2:B10,C1) will return the number of cells in B2:B10 that match the text value or the array of text values in C1.
- Using the SUMPRODUCT function to perform a logical test on multiple arrays and return the sum of the products. For example, =SUMPRODUCT(–(B2:B10=C1)) will return the number of cells in B2:B10 that match the text value or the array of text values in C1.
- Using the FILTER function to return the values from a range that meet one or more criteria. For example, =FILTER(A2:B10,OR(B2:B10=C1)) will return the values from A2:B10 where the corresponding values in B2:B10 match the text value or the array of text values in C1.
- Using conditional formatting to apply a format to cells that meet a certain condition based on text. For example, you can use a formula like =OR(B2=C1) to highlight the cells in column B that match the text value or the array of text values in C1.
