IFS Function in Excel

Johnny Hale 20 Reputation points
2025-06-22T16:13:23.8466667+00:00

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,")

User's image

What is the problem with this formula?

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer accepted by question author

AI answer

2025-06-22T16:13:23.94+00:00

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:

AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

2 people found this answer helpful.

2 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2025-07-18T23:11:56.4633333+00:00

    Hi,

    In cell B2, enter this formula

    =LET(k,$E$2:$E$3,XLOOKUP(TRUE,ISTEXT(REGEXEXTRACT(B2,k,1,1)),k))

    Hope this helps.

    User's image

    Was this answer helpful?

    0 comments
  2. Aditya Tomar 5 Reputation points
    2026-07-21T10:15:46.6033333+00:00

    The issue is that FIND returns a #VALUE! error when the specified text isn't found. This causes the IFS formula to stop evaluating. You can use ISNUMBER(SEARCH(...)) instead, with a final fallback condition, for example:

    =IFS(ISNUMBER(SEARCH("Washer",G3)),"Washer",ISNUMBER(SEARCH("Bolt",G3)),"Bolt",ISNUMBER(SEARCH("Nut",G3)),"Nut",TRUE,"Other")

    This avoids the error and returns "Other" when none of the specified words are found.

    Was this answer helpful?

    2 people found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.