Combining 6 IF Formulas to identify freight carrier

[Knockoutpie]

Hey All,

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", "")

 

[ExceLoki]

try this
---------

=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")

 

[Sufiyan97]

Hey All,

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", "")

Can you please share your sample data,

I will try if this can be done in other way.

 

[Fluff]

Another option

=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", ""))))))

 

[DanteAmor]

You might be interested in the following alternative. In some cells that you have available, put a configuration table like the one I show you below.
At the end of the example you can see the formula:

Dante Amor
	BM	BN	BO	BP	BQ	BR	BS
1				NAME	LEN	LEFT	ISNUMBER
2				UPS	18	1Z	FALSE
3				FedEx	12	*	TRUE
4				DHL	10	*	TRUE
5				Old Dominio	11	06	FALSE
6				Arcbest	9	00	FALSE
7				Averitt	10	00	FALSE
8				Dam	7	*	TRUE
70
71	1Z-456789012345678	UPS
72	123456789012	FedEx
73	1234567890	DHL
74	06-23456789	Old Dominio
75	00-456789	Arcbest
76	00-2345678	Averitt
77	aa-456789012345678	Not Exists
78	1234567	Dam
C

Cell Formulas
Range	Formula
BN71:BN78	BN71	=IFERROR(INDEX($BP$2:$BP$8,SUMPRODUCT(($BQ$2:$BQ$8=LEN(BM71))*(ISNUMBER(SEARCH($BR$2:$BR$8,LEFT(BM71,2))))*($BS$2:$BS$8=ISNUMBER(BM71))*ROW($BP$2:$BP$8))-ROW($BP$2)+1),"Not Exists")

 

[Knockoutpie]

try this
---------

=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")

IFS was my initial approach but I couldn't figure that out so I decide to nest some IF statements, Thank you for this.

Another option

=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", ""))))))

This works too, thank you! Always love seeing replies from you.

You might be interested in the following alternative. In some cells that you have available, put a configuration table like the one I show you below.
At the end of the example you can see the formula:

Dante Amor
	BM	BN	BO	BP	BQ	BR	BS
1				NAME	LEN	LEFT	ISNUMBER
2				UPS	18	1Z	FALSE
3				FedEx	12	*	TRUE
4				DHL	10	*	TRUE
5				Old Dominio	11	06	FALSE
6				Arcbest	9	00	FALSE
7				Averitt	10	00	FALSE
8				Dam	7	*	TRUE
70
71	1Z-456789012345678	UPS
72	123456789012	FedEx
73	1234567890	DHL
74	06-23456789	Old Dominio
75	00-456789	Arcbest
76	00-2345678	Averitt
77	aa-456789012345678	Not Exists
78	1234567	Dam
C

Cell Formulas
Range	Formula
BN71:BN78	BN71	=IFERROR(INDEX($BP$2:$BP$8,SUMPRODUCT(($BQ$2:$BQ$8=LEN(BM71))*(ISNUMBER(SEARCH($BR$2:$BR$8,LEFT(BM71,2))))*($BS$2:$BS$8=ISNUMBER(BM71))*ROW($BP$2:$BP$8))-ROW($BP$2)+1),"Not Exists")

So creative, i'm going to mess around with this, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.