IFS Function in Excel

Question

I want search a column of data and select a words with three logical expressions. It works for the first true expression, but not for the other two. Here is the data and formula:

=IFS(FIND("Washer",G3,1)>0,"Washer,",FIND("Bolt",G3,1)>0,"Bolt,",FIND("Nut",G3,1)>0,"Nut,")

What is the problem with this formula?

Answer 1

The issue with your formula is that the FIND function returns an error if the specified text is not found, which can cause the IFS function to stop evaluating further conditions. The IFS function evaluates each condition in order and returns the result for the first condition that is true. If the first condition fails (i.e., if "Washer" is not found), it will not evaluate the subsequent conditions for "Bolt" and "Nut".

To handle this, you can use the ISNUMBER function to check if the FIND function returns a number (indicating that the text was found) before proceeding. Here’s how you can modify your formula:

=IFS(ISNUMBER(FIND("Washer",G3,1)),"Washer,", ISNUMBER(FIND("Bolt",G3,1)),"Bolt,", ISNUMBER(FIND("Nut",G3,1)),"Nut,")

This way, each condition will only be evaluated if the FIND function does not return an error.

---

References:

• How to use a logical AND or OR in a SUM+IF statement in Excel
• Custom function may not calculate expected value in Excel