IF - ELSE IF - ELSE Structure in Excel

excel excel-formula
RazrBoy picture RazrBoy · May 10, 2012 · Viewed 275.4k times · Source

Requirement :

If the string in cell A1 contains "abc" as part of the string

Then assign value "Green"

Else if the string in cell A1 contains "xyz" as part of the string

Then assign value "Yellow"

Else

Assign value "Red"

I tried this :

=IF(FIND("~",SUBSTITUTE(A1,"abc","~",1))<>"#VALUE!", "Green", IF(FIND("~",SUBSTITUTE(A1,"xyz","~",1))<>"#VALUE!", "Yellow", "Red"))

It works if first IF is True in other cases it gives #VALUE! as output.

Can't figure out whats wrong.

Answer

assylias picture assylias · May 10, 2012

When FIND returns #VALUE!, it is an error, not a string, so you can't compare FIND(...) with "#VALUE!", you need to check if FIND returns an error with ISERROR. Also FIND can work on multiple characters.

So a simplified and working version of your formula would be:

=IF(ISERROR(FIND("abc",A1))=FALSE, "Green", IF(ISERROR(FIND("xyz",A1))=FALSE, "Yellow", "Red"))

Or, to remove the double negations:

=IF(ISERROR(FIND("abc",A1)), IF(ISERROR(FIND("xyz",A1)), "Red", "Yellow"),"Green")

Related questions

Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More