Formula Help

[lfreeman25]

I need to find out how to use dashes/hyphens in an IF formula without Excel trying to treat them like a minus sign. Ex: if cell A1 says "blue", then I want cell A2 to say "799-69999-1" (exactly like this with the hyphens). However, cell A2 is showing -59301. How do I make the end result in cell A2 say "799-69999-1"? I also want to find a formula that will allow me to input multiple criteria. Ex: if cell A1 says blue, then cell A2 should be 799-99999-1. If cell A1 says red, then cell A2 should be 788-88888-1, etc. it would be up to 10 different criteria. Any help would be appreciated!

[#2]

@lfreeman25 

Because you can only use a maximum of 7 layers  in a nested IF statement, below is a tip from John Walkenbach:

How to use more than 7 Nested IF statements:

Source: John Walkenbach

Using the CONCATENATE function

Yet another option was suggested to me by B. Ganesh: Use the CONCATENATE function. In this case, each argument for CONCATENATE consists of an IF function. Here's an example:

=CONCATENATE(IF(A1="A",1,""),IF(A1="B",2,""),IF(A1="C",3,""),

IF(A1="D",4,""),IF(A1="E",5,""),IF(A1="F",6,""),IF(A1="G",7,""),

IF(A1="H",8,""),IF(A1="I",9,""),IF(A1="J",10,""))

The CONCATENATE function can handle as many as 30 arguments -- which equates to testing 30 different conditions.

And, as Alan Williams pointed out, you can avoid using the CONCATENATE function and use the concatenation operator (&):

   =IF(A1="A",1,"")&IF(A1="B",2,"")&IF(A1="C",3,"")

    &IF(A1="D",4,"")&IF(A1="E",5,"")&IF(A1="F",6,"")

   &IF(A1="G",7,"")&IF(A1="H",8,"")&IF(A1="I",9,"")

   &IF(A1="J",10,"")

This method is not limited to 30 comparisons.

[#3]

@Dutchman54 , limit of 7 layers is for Excel 2003 and below, starting from 2007 the limit is 64. Modern IFS allows up to 127 condition.

However, in this case better to have helper table which maps one on another and use VLOOKUP, LOOKUP or INDEX/MATCH.

When it'll be no side effect when

=IF(1,788-88888-1,)

where IF interprets 788-88888-1 as =788-88888-1 and returns -88101.

If to hardcode when

=IF(1,"788-88888-1",)