CODEWITHSUNDEEP
excelexcel-formula
Knockoutpie
Knockoutpie

Reputation: 103

Combining 6 IF formulas

I've written 6 different IF formulas, each will identify a freight carrier based on the tracking number found in cell BM71.

For the life of me I cannot figure out how to combine these, any help would be appreciated.

=IF(AND(LEN(BM71)=18,LEFT(BM71,2)="1Z"), "UPS", "")
=IF(AND(LEN(BM71)=12,ISNUMBER(BM71)),"FedEx","")
=IF(AND(LEN(BM71)=10,ISNUMBER(BM71)),"DHL","")
=IF(AND(LEN(BM71)=11,LEFT(BM71,2)="06"), "Old Dominion", "")
=IF(AND(LEN(BM71)=9,LEFT(BM71,2)="00"), "Arcbest", "")
=IF(AND(LEN(BM71)=10,LEFT(BM71,2)="00"), "Averitt", "")

Upvotes: 0

Views: 70

Answers (4)

DS_London
DS_London

Reputation: 4261

No love for LET?

Something like:

=LET(x,BM71,l,LEN(x),b,ISNUMBER(x),s,LEFT(x,2),IFS(
(l=18)*(s="1Z"),"UPS",
(l=12)*b,"FedEx",
(l=10)*b,"DHL",
(l=11)*(s="06"),"Old Dominion",
(l=9)*(s="00"),"Arcbest",
(l=10)*(s="00"),"Averitt"))

If nothing else it cuts down the formula length, and the function only takes one input cell reference (rather than 12 ...).

EDIT: Though if it were me (as other comments have mentioned) I would use this:

=LET(x,BM71,l,LEN(x),b,ISNUMBER(x),s,LEFT(x,2),c,IFS(
(l=18)*(s="1Z"),1,
(l=12)*b,2,
(l=10)*b,3,
(l=11)*(s="06"),4,
(l=9)*(s="00"),5,
(l=10)*(s="00"),6,
TRUE,7),INDEX(Carriers,c) )

With the range named Carriers holding the list of carriers, with a blank in the last row. Makes it easier to change a carrier name and you keep the list in one place which can be re-used by other formulas. (But then I am a devotee of the Third Normal Form ...)

Upvotes: 2

WeAreOne
WeAreOne

Reputation: 1343

With ifS function it gets more clean:

 =IFs(AND(LEN(BM71)=18,LEFT(BM71,2)="1Z"), "UPS", 
    AND(LEN(BM71)=12,ISNUMBER(BM71)),"FedEx",
    AND(LEN(BM71)=10,ISNUMBER(BM71)),"DHL",
    AND(LEN(BM71)=11,LEFT(BM71,2)="06"), "Old Dominion",
    AND(LEN(BM71)=9,LEFT(BM71,2)="00"), "Arcbest",
    AND(LEN(BM71)=10,LEFT(BM71,2)="00"), "Averitt",
True,"")

Upvotes: 4

Nikita
Nikita

Reputation: 822

You can add next one instead of "" of previous formula

=IF(AND(LEN(BM71)=18,LEFT(BM71,2)="1Z"), "UPS", IF(AND(LEN(BM71)=12,ISNUMBER(BM71)),"FedEx",IF(AND(LEN(BM71)=10,ISNUMBER(BM71)),"DHL",IF(AND(LEN(BM71)=11,LEFT(BM71,2)="06"), "Old Dominion", IF(AND(LEN(BM71)=9,LEFT(BM71,2)="00"), "Arcbest", IF(AND(LEN(BM71)=10,LEFT(BM71,2)="00"), "Averitt", ""))))))

However you approach to define something based on length and some chars is not stable I hope I could help you

Upvotes: 1

Horaciux
Horaciux

Reputation: 6477

You just add a new IF in the False part. Like this:

=IF(AND(LEN(BM71)=18,LEFT(BM71,2)="1Z"), "UPS",
IF(AND(LEN(BM71)=12,ISNUMBER(BM71)),"FedEx",
IF(AND(LEN(BM71)=10,ISNUMBER(BM71)),"DHL",
IF(AND(LEN(BM71)=11,LEFT(BM71,2)="06"), "Old Dominion",
IF(AND(LEN(BM71)=9,LEFT(BM71,2)="00"), "Arcbest",
IF(AND(LEN(BM71)=10,LEFT(BM71,2)="00"), "Averitt", ""))))))

Upvotes: 2

Related Questions