Table of Contents
The FIND function in Excel allows users to search for specific text within a cell or range of cells. This function can be particularly useful when trying to find data that meets multiple criteria. By using the FIND function in combination with other functions or formulas, users can effectively narrow down their search results and retrieve the exact data they need. This can save time and effort, especially when dealing with large datasets. To utilize the FIND function for multiple criteria, users can specify the different criteria in separate cells and use logical operators such as AND or OR to combine them in their formula. This will enable the FIND function to search for data that meets all the specified criteria, providing more precise results. Employing the FIND function for multiple criteria can greatly enhance the efficiency and accuracy of data analysis and management in Excel.
Excel: Use FIND Function with Multiple Criteria
You can use the FIND function in Excel to find the position of the first occurrence of a specific character in a string.
However, sometimes you may wish to use the FIND function to search for the first occurrence of one of several characters in a string.
You can use the following formula to do so:
=FIND("a",SUBSTITUTE(SUBSTITUTE(A2,"b","a"),"c","a"))
This particular formula searches cell A2 and returns the position of the first occurrence of any of the following characters:
- a
- b
- c
If none of these characters is found in cell A2, then the formula returns #VALUE! as a result.
The following example shows how to use this formula in practice.
Example: How to Use FIND Function with Multiple Criteria in Excel
Suppose we have the following list of basketball team names in Excel:
Now suppose we would like to find the position of the first occurrence of either an a, b, or c in each team name.
We can type the following formula into cell B2 to do so:
=FIND("a",SUBSTITUTE(SUBSTITUTE(A2,"b","a"),"c","a"))
We can then click and drag this formula down to each remaining cell in column B:
Column B displays the position of the first occurrence of either an a, b, or c in each team name.
- The first position of an a, b, or c in Mavericks is in position 2.
- The first position of an a, b, or c in Cavs is in position 2.
- The first position of an a, b, or c in Celtics is in position 6.
Note that the FIND function is case-sensitive.
How This Formula Works
Recall the formula that we used to find the position of the first occurrence of either an a, b, or c in cell A2:
=FIND("a",SUBSTITUTE(SUBSTITUTE(A2,"b","a"),"c","a"))
Here is how this formula works:
First, we use the SUBSTITUTE function to substitute the occurrence of each b in cell A2 with an a instead.
Then we use another SUBSTITUTE function to substitute the occurrence of each c in cell A2 with an a instead.
Lastly, we use the FIND function to simply find the position of the first occurrence of an a in cell A2, which is now equivalent to finding the position of the first occurrence of a, b, or c.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: