VBA or Formula for Conditional Hyperlinks

[kmichael]

I have a variety of carriers I am wanting to insert tracking links based on tracking information. See below

I am hoping to use the number entered in Column J to search the appropriate tracking site.
I have Column I with a drop down of possible options.
These are the links I am trying to use for pulling data:

https://www.fedex.com/fedextrack/?trknbr=(J2)

Track a Shipment | XPO Logistics

XPO (NYSE: XPO) is one of the largest providers of asset-based less-than-truckload (LTL) transportation in North America, with proprietary technology that moves goods efficiently through its network. Together with its business in Europe, XPO serves approximately 43,000 shippers with 564...

www.xpo.com

Tracking | UPS - United States

Track one or multiple packages with UPS Tracking, use your tracking number to track the status of your package.

wwwapps.ups.com

Tracing - Southeastern Freight Lines

www.sefl.com

Please help me make this easier for me to track.

 

[etaf]

not sure if this will work
I'm assuming the tracking number
=HYPERLINK("https://www.fedex.com/fedextrack/system-error?trknbr=("&J2&")")
I'm assuming the tracking number is within the brackets

so you would need the links in a table to be able to then lookup and add the shipping number

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S
1		Shipper	Number	URL						LOOKUP TABLE
2		fedex	1234	https://www.fedex.com/fedextrack/system-error?trknbr=(1234)						fedex	https://www.fedex.com/fedextrack/system-error?trknbr=(
3		SFL	abc	https://www.sefl.com/webconnect/tracing?Type=PN&RefNum1=(abc)
4		UPS	abc1234	https://wwwapps.ups.com/WebTracking/track?track=yes&trackNums=(abc1234)						UPS	https://wwwapps.ups.com/WebTracking/track?track=yes&trackNums=(	)&requester=ST/
5
6										SFL	https://www.sefl.com/webconnect/tracing?Type=PN&RefNum1=(
7
Sheet1

Cell Formulas
Range	Formula
K2:K4	K2	=HYPERLINK(INDEX(R2:R6,MATCH(I2,Q2:Q6,0))&J2&")")

 

[kmichael]

Yes...worked perfectly. Thank you so much.
The explanation also made it easy for me to adjust for more carriers if needed too

 

[kmichael]

Actually, This works great for each first tracking number with each carrier but isn't creating url for variations within column J that have the same selection in column K

 

[etaf]

can you give examples please

 

[kmichael]

Currently have this formula in Column K: =HYPERLINK(INDEX(Z2:Z5,MATCH(I2,Y2:Y5,0))&J2&")")
But it is only matching to first FedEx and creating hyperlink then others appear as N/A

 

[Sufiyan97]

Try fixing your ranges

=HYPERLINK(INDEX($Z$2:$Z$5,MATCH(I2,$Y$2:$Y$5,0))&J2&")")

 

[kmichael]

Awesome thanks so much

 

[Sufiyan97]

You're welcome.