Vlookup with if function

[censo]

Hello,

I'd like assistance with figuring out how to determine the name of a carrier based on how the first few characters of a tracking ID begin with. The problem is some characters can instantly identify a carrier by the first character and other can be identified by 2 or 3 characters.

Lastly, if none of the criteria match, instead of outputting an N/A value, I'd prefer it to default to "FedEx".

The source data is column C "Tracking ID"

Columns H:I is a named range to be used in the function, entitled "CARRIER_TBL"

Column A "Carrier" is the intended output.

Observe how Lasership can be identified as either "1LS" or "LS", and

UPS can be identified with either "1Z" or "Z"

Lastly, "FedEx" was the correct output when the tracking ID for C8 could not be found (because FedEx has so many different variations to their tracking IDs, this is the preferred catch-all method)

Book1
	A	B	C	D	E	F	G	H	I
1	Carrier		Tracking ID					CARRIER_TBL
2	Lasership		1LS563210865342					1LS	Lasership
3	Lasership		LS720841130957					LS	Lasership
4	UPS		1ZE336250366090349					1Z	UPS
5	UPS		ZE336250366090349					Z	UPS
6	United Delivery Service		WM6280000000919759					C	OnTrac
7	OnTrac		C11659806311237					WM	United Delivery Service
8	FedEx		429154527459
Sheet2

 

[Aladin Akyurek]

In A2 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("|"&$H$2:$H$7,"|"&$C2),$I$2:$I$7),"FedEx")

 

[censo]

In A2 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("|"&$H$2:$H$7,"|"&$C2),$I$2:$I$7),"FedEx")

Oh boy, the problem is I've only situated the lookup table where it is for demonstration purposes only. The real array will reside on another "Reference" tab, that's why I preferred to have it a named range vs. hard coding it in because then it wouldn't matter where it resides.

I'll attempt to modify your solution to its new location and see if I can get that to work.

Thanks

 

[Aladin Akyurek]

Oh boy, the problem is I've only situated the lookup table where it is for demonstration purposes only. The real array will reside on another "Reference" tab, that's why I preferred to have it a named range vs. hard coding it in because then it wouldn't matter where it resides.

I'll attempt to modify your solution to its new location and see if I can get that to work.

Thanks

If CARRIER_TBL refers to the 2-column table...

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("|"&INDEX(CARRIER_TBL,0,1),"|"&$C2),INDEX(CARRIER_TBL,0,2)),"FedEx")

will do just as well.​

 

[censo]

I'll attempt to modify your solution to its new location and see if I can get that to work.

Ok, so far so good. I modified the function to point to the new location of the array and it's working like a charm.

I'd like one slight modification and that is to not generate "FedEx" when there is no data present (in other words, remain blank).

What would the new function look like? This is what I've modified the function to in my production environment and is working good.

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("|"&INDEX(CARRIER_TBL,0,1),"|"&$G2),INDEX(CARRIER_TBL,0,2)),"FedEx")

 

[Aladin Akyurek]

Do you mean when there is nothing in G2?

 

[censo]

Do you mean when there is nothing in G2?

Yes!

 

[Aladin Akyurek]

Do you mean when there is nothing in G2?

Yes!

We need to test G2 for being blank...

=IF($G2="","",IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("|"&INDEX(CARRIER_TBL,0,1),"|"&$C2),INDEX(CARRIER_TBL,0,2)),"FedEx"))​

 

[censo]

We need to test G2 for being blank...

​

Nailed it.

There was just a C2 cell reference I had to correct to G2, but that most definitely did the trick. Thank you much and this thread may be considered 'ANSWERED'​

 

[Aladin Akyurek]

@ censo

You are welcome.