Apr 17 2019 12:38 PM
Apr 17 2019 01:18 PM
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.
Apr 17 2019 01:45 PM
@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",)