INDEX MATCH as HYPERLINK email address

[blackburnsexcel]

Hi,

I'm using INDEX and MATCH to look up details in another worksheet.

Part of the info is an email address. The only problem is that the email address isn't hyperlinked. Is there any way to achieve this?

Sheet 1 is several columns. Company, name, telephone, email address. Also a column (H) that has a drop down menu with 2 options. Yes, or No. This formula counts the yes' and is in column I which is hidden.

Please Login or Register  to view this content.

Sheet 2 then draws all rows with a yes by using this formula

Please Login or Register  to view this content.

I just need to figure out where to place the hyperlink in the above so when the email address is drawn from the first sheet it shows as a hyperlink in the second sheet.

Any help would be appreciated.

[Glenn Kennedy]

This is untested:

=IF(ISERROR(HYPERLINK("mailto:"&INDEX('Sheet 1'!A$2:A$1000, MATCH("Y_"&ROWS($A$1:$A1),'Sheet 1'!$I$2:$I$1000,0)))),"",HYPERLINK("mailto:"&INDEX('Sheet 1'!A$2:A$1000, MATCH("Y_"&ROWS($A$1:$A1),'Sheet 1'!$I$2:$I$1000,0))))

If it doesn't work, can you post a sample sheet? Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

[blackburnsexcel]

This is untested:

=IF(ISERROR(HYPERLINK("mailto:"&INDEX('Sheet 1'!A$2:A$1000, MATCH("Y_"&ROWS($A$1:$A1),'Sheet 1'!$I$2:$I$1000,0)))),"",HYPERLINK("mailto:"&INDEX('Sheet 1'!A$2:A$1000, MATCH("Y_"&ROWS($A$1:$A1),'Sheet 1'!$I$2:$I$1000,0))))

If it doesn't work, can you post a sample sheet? Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

It didn't work unfortunately. workbook attached.Copy of Lapsed Customers V3.xls

[Glenn Kennedy]

OK. I tried this formula (not entirely sure if it's the same as the previous one), substituting a real email address for one of the fakes. It worked perfectly to my gmail account. I will not publish my email address online (not being a lover of spam). Try it in place in the formula using your own email. If it fails, we can exchange email address by PM and I can send you a form where I KNOW it is working...

=IF(ISERROR(HYPERLINK("mailto:"&INDEX('Lapsed Customers'!G$2:G$407,MATCH("Y_"&ROWS($A$1:$A1),'Lapsed Customers'!$I$2:$I$407,0)))),"",HYPERLINK("mailto:"&INDEX('Lapsed Customers'!G$2:G$407,MATCH("Y_"&ROWS($A$1:$A1),'Lapsed Customers'!$I$2:$I$407,0))))

[blackburnsexcel]

OK. I tried this formula (not entirely sure if it's the same as the previous one), substituting a real email address for one of the fakes. It worked perfectly to my gmail account. I will not publish my email address online (not being a lover of spam). Try it in place in the formula using your own email. If it fails, we can exchange email address by PM and I can send you a form where I KNOW it is working...

=IF(ISERROR(HYPERLINK("mailto:"&INDEX('Lapsed Customers'!G$2:G$407,MATCH("Y_"&ROWS($A$1:$A1),'Lapsed Customers'!$I$2:$I$407,0)))),"",HYPERLINK("mailto:"&INDEX('Lapsed Customers'!G$2:G$407,MATCH("Y_"&ROWS($A$1:$A1),'Lapsed Customers'!$I$2:$I$407,0))))

That did it, it looks identical to me. Must have been me.

Thanks very much.

[Glenn Kennedy]

Woo Hoo!! Glad to have helped and thanks for the Rep.