IF(OR....Alternatives

[mrsushi]

Good morning,

I have the following formula which appears to only limit the number of items within the statement. I cant seem to fit anymore IF statements within this formula. Is there an alternative where i can add more?

=IF(OR(N2="Rimes"),CONCATENATE(C2,D2,".CSV"),IF((N2="FTSE"),CONCATENATE(C2,E2,".CSV"),IF((N2="Barclays"),CONCATENATE(C2,F2,".CSV"),IF((N2="Bloomberg"),CONCATENATE(C2,G2,".CSV"),IF((N2="Iboxx"),CONCATENATE(C2,H2,".CSV"),IF((N2="ICEBoAML"),CONCATENATE(C2,I2,".CSV"),IF((N2="Markit"),CONCATENATE(C2,J2,".CSV"),"Check File Name")))))))

Many thanks
M

 

[Joe4]

One way would be to build your own User Defined Function (UDF) in VBA, where you can add as many options as you like. The code would look something like this:

Function BuildFileName(str As String) As String

    Dim addr As String
    Dim fName As String
    
    Select Case str
        Case "Rimes"
            addr = "D2"
        Case "FTSE"
            addr = "E2"
        Case "Barclays"
            addr = "F2"
        Case "Bloomberg"
            addr = "G2"
        Case "Iboxx"
            addr = "H2"
        Case "ICEBoAML"
            addr = "I2"
        Case "Markit"
            addr = "J2"
        Case Else
            addr = "Check File Name"
    End Select
    
    If addr = "Check File Name" Then
        BuildFileName = addr
    Else
        BuildFileName = Range("C2") & addr & ".CSV"
    End If
    
End Function

Then you would just use it on your sheet like any other Excel function, i.e.

=BuildFileName(N2)

 

[Kerryx]

There is a limit of about 64 nested IF arguments allowed and you are far from that, how many companies are you thinking of adding?
If there are only a few more then just add extra like line in Bold below and change concatenate cell as required, also remember to add closing bracket for every argument you add
=IF(OR(N2="Rimes"),CONCATENATE(C2,D2,".CSV"),IF((N2="FTSE"),CONCATENATE(C2,E2,".CSV"),IF((N2="Barclays"),CONCATENATE(C2,F2,".CSV"),IF((N2="Bloomberg"),CONCATENATE(C2,G2,".CSV"),IF((N2="Iboxx"),CONCATENATE(C2,H2,".CSV"),IF((N2="ICEBoAML"),CONCATENATE(C2,I2,".CSV"),IF((N2="Markit"),CONCATENATE(C2,J2,".CSV"),IF((N2="XXXXX"),CONCATENATE(C2,K2,".CSV"),"Check File Name"))))))))

 

[Joe4]

There is a limit of about 64 nested IF arguments allowed and you are far from that, how many companies are you thinking of adding?
If there are only a few more then just add extra like line in Bold below and change concatenate cell as required, also remember to add closing bracket for every argument you add
=IF(OR(N2="Rimes"),CONCATENATE(C2,D2,".CSV"),IF((N2="FTSE"),CONCATENATE(C2,E2,".CSV"),IF((N2="Barclays"),CONCATENATE(C2,F2,".CSV"),IF((N2="Bloomberg"),CONCATENATE(C2,G2,".CSV"),IF((N2="Iboxx"),CONCATENATE(C2,H2,".CSV"),IF((N2="ICEBoAML"),CONCATENATE(C2,I2,".CSV"),IF((N2="Markit"),CONCATENATE(C2,J2,".CSV"),IF((N2="XXXXX"),CONCATENATE(C2,K2,".CSV"),"Check File Name"))))))))

If they are using Excel 2010 (which is what their profile says), it may be less than that.
Older versions of Excel only allowed nesting up to 7 levels.

 

[Kerryx]

True Joe all depends on Excel version thought it was 7 up to version 2007 and 64 after that but may be wrong

 

[Fluff]

Another option is

=IFNA(CONCATENATE(C2,INDEX(D2:J2,MATCH(N2,{"Rimes","FTSE""Barclays","Bloomberg","Iboxx","ICEBoAML","Markit"},0)),".CSV"),"Check file name")