Dynamic Hyperlink Creator

[censo]

I have an existing function that creates a hyperlink because the syntax of that URL is baked into the function. This works perfectly fine for a single website; however, the solution becomes obsolete and inaccurate when multiple websites are thrown into the mix.


In the sample below, Column A is the source data and Column B is the desired result with the use of said function.

Book1
	A	B
1	SKU	SUPP PAGE
2	WAL-22880494	http://www.walmart.com/ip/22880494
3	WAL-12443829	http://www.walmart.com/ip/12443829
4
5
6	SKU	DESIRED RESULT (MIX OF ALL SUPPLIERS)
7	SAM-prod15680245	https://www.samsclub.com/sams/prod15680245.ip
8	SAM-prod17580146	https://www.samsclub.com/sams/prod17580146.ip
9	COS-100415587	https://www.costco.com/.product.100415587.html
10	COS-100376396	https://www.costco.com/.product.100376396.html
11	WAL-22880494	http://www.walmart.com/ip/22880494
12	WAL-12443829	http://www.walmart.com/ip/12443829
DATA

Cell Formulas
Range	Formula
B2	=HYPERLINK("http://www.walmart.com/ip/"&MID(A2,5,FIND("-",A2&"-",5)-5))

The function successfully extracts the item # from the SKU and concatenates it with a pre-defined syntax, but what I need it to do now is the following:


#1 - Identify the SUPP prefix (will ALWAYS be the first 3 characters of the SKU)


#2 - refer to a lookup table to find the supp code and use the syntax associated with the supplier it found


#3 - Within the syntax it found, find the instance where the phrase "ITEM" is present and replace it with the item number extracted

Book1
	A	B
1	SUPP CODE	SUPP SYNTAX
2	WAL	http://www.walmart.com/ip/ITEM
3	SAM	https:www.samsclub.com/sams/ITEM.ip
4	COS	https://www.costco.com/.product.ITEM.html
REFERENCE

I'm not too versed in functions above my current level of understanding but I made a valiant attempt anyway. I think where the cell reference "A2" is present the MID function below should be inserted for one part but then another function like =LEFT(A2,3) should be used to identify the supplier somewhere else - but yea, that didn't work for me


Here are the functions that I think need to be in play and need to be joined together successfully

=LEFT(A2,3)

Identifies the Supplier

=MID(A2,5,FIND("-",A2&"-",5)-5)

Extracts the Item Number from the SKU

=HYPERLINK(REPLACE(VLOOKUP(A2,SUPP_SYNTAX,2,0),FIND("ITEM",(VLOOKUP(A2,SUPP_SYNTAX,2,0))),4,A2))

Finds the correct syntax and replaces "ITEM" with the correct Item Number from the SKU


I feel like I'm close, what am I missing?


Thanks.

 

[Eric W]

Try:

=HYPERLINK(SUBSTITUTE(VLOOKUP(LEFT(A2,3),Supp_syntax,2,0),"ITEM",MID(A2,5,99)))

 

[dreid1011]

How about this? Col K contains the SUPP SYNTAX in the lookup table, and Col J contains the SUPP CODE.

=HYPERLINK(REPLACE(INDEX($K$3:$K$5,MATCH(LEFT(A7,3),$J$3:$J$5,0)),FIND("ITEM",INDEX($K$3:$K$5,MATCH(LEFT(A7,3),$J$3:$J$5,0))),4,RIGHT(A7,LEN(A7)-4)))

I tried it with VLOOKUP, but it was returning the wrong result for "SAM". And the lookup table for VLOOKUPs have to be sorted properly, whereas INDEX/MATCH does not.

Edit: Of course, after the fact, the VLOOKUP works just fine, and Eric has provided a much nicer formula.

 

[censo]

Try:

=HYPERLINK(SUBSTITUTE(VLOOKUP(LEFT(A2,3),Supp_syntax,2,0),"ITEM",MID(A2,5,99)))

SUCCESS! The submitted solution works perfectly. Thank you so much.

 

[censo]

Edit: Of course, after the fact, the VLOOKUP works just fine, and Eric has provided a much nicer formula.

Yes, Eric's submission nailed it on the first shot. I appreciate your effort as well, thanks.

 

[Eric W]

Glad it works for you!

Incidentally, VLOOKUP does not require the table to be sorted, if you set the optional range_lookup parameter to FALSE, or 0.