Excel Identifier

[geoffsmile]

Hello Everyone,

I want to learn if there is a formula that can search and provides a result, according to the set result that involves nesting. As seen on the table, I have a list of Merchant Tracking Numbers. All distinct on each way. UPS always starts its tracking number as "1Z" and USPS as "940". Please help me correct the formula which will help identify the tracking numbers on column "A". Let me know what am I doing wrong.

Merchant Tracking Numbers	True Courier	Formula For Indentifiying	Formula Result
1ZWF00980394816384	UPS	=IF(SEARCH("1Z",A2),"UPS",IF(FIND("940",A2),"USPS","FedEx Ground"))	UPS
9400115901195256406673	USPS	=IF(SEARCH("1Z",A3),"UPS",IF(FIND("940",A3),"USPS","FedEx Ground"))	#VALUE!
604209703070	FedEx	=IF(SEARCH("1Z",A4),"UPS",IF(FIND("940",A4),"USPS","FedEx Ground"))	#VALUE!

<tbody>
</tbody>

Appreciate the Help! Cheers!

 

[Rick Rothstein]

As long as those will be the only three carriers, and as long as the shipment is not one of those hybrids where the UPS or FedEx starts the package and the US Post Office completes the delivery, then this formula should work...

=IF(LEFT(A1,2)="1Z","UPS",IF(LEFT(A1,3)="940","USPS","FedEx"))

 

[jim may]

Excel 2012
	A	B
1	Merchant Tracking Numbers	Formula Result
2	1ZWF00980394816384	UPS
3	9400115901195256406673	USPS
4	604209703070	FedEx
Sheet1

Cell Formulas
Range	Formula
B2	=IF(LEFT(A2,2)="1Z","UPS",IF(LEFT(A2,3)="940","USPS","FedEx"))

 

[Rick Rothstein]

Worksheet Formulas Cell Formula B2 =IF(LEFT(A2,2)="1Z","UPS",IF(LEFT(A2,3)="940","USPS","FedEx")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Something is wrong with your formula (see where I highlighted it in red). It looks like you were going for what I posted in Message #2.

 

[geoffsmile]

This works. How come I didn't bother to use the LEFT formula. Now I can finish my report with ease! Shout out to "Rick Rothstein" and "
jim may", you guys are a life saver!!

 

[jim may]

Actually, I'm not seeing any difference in our formulas. It might be poor eye-sight, or encroaching old-age on my part, bUT anyway .. Happy 4th!! Jim