Linking e-mail address in the VLOOKUP function:

Recently, I was asked by Carine Binnenmarsch-Thunder, how to link an email address in the VLOOKUP function.
In this tip, I will explain how you can do this.

I’m not going to explain how to use a validation list and the VLOOKUP function here.
You can do this by going to Lesson 1 and Lesson 2 of the “Excel Automation” course.

I get the validation list in cell B1 of “Sheet1” of the worksheet.

The validation list in the “List” worksheet has a range named “employees”.

To enter the underlying fields, we use the “VLOOKUP” function.
The address should be in the third column of the “workers” range.

The “Province” must go to the fourth column of the “Workers” range.

And, the email address should go to the fifth column of the “workers” range.
Everybody should know that.

The problem however is, or rather was, that the email address is no longer a hyperlink and, is not displayed as an e-mail address but, just as text.

We solve this by the using the HYPERLINK function along with the VLOOKUP function.
Type: = HYPERLINK (“mailto:” &
What does this mean?
HYPERLINK is for Excel to know that it must create a Hyperlink for this data.
“Mailto:”, is for Excel to know that an e-mail is to be linked to this data.
The ampersand is present just to place both functions together.

Now click on the text in cell B4 and then click the e-mail address so that your email software will automatically open a new message.

Important:
If you want to do same thing for an Internet address, you need to type = HYPERLINK (“http://” &the VLOOKUP function
For example:
= HYPERLINK (“http://” & VLOOKUP ($ B $ 1, employees, 8, 0))

FYI: Swotster.com is thinking of you, so you also please remember swotster.com

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats