Field lookup and mapping to create URL

**RAuten** · 02-16-2010, 11:36 AM

Hi,

Pretty familiar with using Excel for the basics, but need some help doing some mapping to create a new field.

What I want to create is a new field at the end of each Excel record containing a URL in the following format: http://www.mywebsite.com/Shared/Region/State/12345-license.pdf

12345 - is a code contained in column B. Just need to know the syntax for referencing it.
State - we want the spelled out name, but we only have the two-character abbreviation in column H. So if column H contains CT, we want to put 'Connecticut' in the URL in the last column.
Region - is based on state. Valid values are Midwest, West, South, Northeast. I need to do a lookup on the state to determine which region to list in the URL.

Any suggestions would be greatly appreciated. Thanks in advance!

**DonkeyOte** · 02-16-2010, 01:39 PM

Assuming you want the URL to be a live link, see the HYPERLINK function.

Regards State/Region - setup a reference table with 2 char codes in Col A, full name in B and Region in C.

You can conduct two VLOOKUPs off the 2 char code state code to retrieve description & region and embed these into your HYPERLINK function.

Assume your lookup table is on a new sheet called STATEDATA and on the current sheet state code is in H2, code is in B2 with URL to go into I2:

I2: =HYPERLINK("http://www.mywebsite.com/Shared/"&VLOOKUP($H2,STATEDATA!$A:$C,3,0)&"/"&VLOOKUP($H2,STATEDATA!$A:$B,2,0)&"/"&$B2&"-license.pdf",$B2&"-license.pdf")