Formula wont display correct name

[katralic]

excel 2010
I have this formula:

=IFERROR(IF(VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)),"")

Which returns a persons email address stored in a database. Works great. I want to be able to click on that email address and send mail so I did this:

=HYPERLINK("mailto:"&IFERROR(IF(VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)),"")&"?"

All I did was add =HYPERLINK("mailto:"& to the beginning and &"?" at the end. It works fine. The problem I'm having is with the original formula it displayed the email address.
The new formula displays a hyperlink with the name "link". I want the hyperlink to display the actual email address.
Any suggestions

 

[Fluff]

How about

=HYPERLINK("mailto:"&IFERROR(IF(VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)),"")&"?",IFERROR(IF(VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)),""))

 

[katralic]

How about

=HYPERLINK("mailto:"&IFERROR(IF(VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)),"")&"?",IFERROR(IF(VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)),""))

Nope, didn't work. It opens mail but doesn't put the persons email address in the "to:" and also doesn't display anything in excel

 

[RoryA]

Works fine here. If you're not seeing anything in the cell, then your VLOOKUP didn't return a match.

 

[Fluff]

What happens if you use

=HYPERLINK("mailto:"&IFERROR(IF(VLOOKUP(H$3,'Customer info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer info'!G$2:AZ$1494,8,FALSE)),"")&"?",IFERROR(IF(VLOOKUP(H$3,'Customer info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer info'!G$2:AZ$1494,8,FALSE)),"not found"))

 

[katralic]

Works fine here. If you're not seeing anything in the cell, then your VLOOKUP didn't return a match.

Oh crap I figured it out. In my formula I misspelled customer as custumer. When I posted this I corrected the spelling with the intent on fixing it in excel also. I forgot to change it to match my misspelling.
It worked Thank you.

 

[Fluff]

Glad it's sorted & thanks for the feedback.

 

[RoryA]

You might also want to wrap the whole thing in one IFERROR so you don't end up with invalid hyperlinks:

=IFERROR(HYPERLINK("mailto:"&IF(VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE))&"?",IF(VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE))),"")

If you had 365 this would be much shorter using LET.

 

[katralic]

You might also want to wrap the whole thing in one IFERROR so you don't end up with invalid hyperlinks:

=IFERROR(HYPERLINK("mailto:"&IF(VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE))&"?",IF(VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE)=0,"",VLOOKUP(H$3,'Customer Info'!G$2:AZ$1494,8,FALSE))),"")

If you had 365 this would be much shorter using LET.

Ya I actually changed it to have the iferror at the beginning but when it didn't work either I changed it back. Now that it works I will change it back again.