How can I simplify this HYPERLINK formula?

J

jmpatrick

Active Member
Joined
Aug 17, 2016
Messages
482
Office Version
  1. 365
Platform
  1. Windows
I have this formula that creates hyperlinks based on the contents in C8. This works fine but it looks rather ugly. It seems like the ))))) characters look clunky. Is there a more elegant way of doing this?

VBA Code: 
=IF(COUNTIF($C8,"*1072*"),HYPERLINK("mailto:chadd.willson@blahblahblah","Chadd Willson"),
IF(COUNTIF($C8,"*1076*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1079*"),HYPERLINK("mailto:scott.bennett@blahblahblah","Scott Bennett"),
IF(COUNTIF($C8,"*1080*"),HYPERLINK("mailto:brian.curry@blahblahblah","Brian Curry"),
IF(COUNTIF($C8,"*1081*"),HYPERLINK("mailto:kyle.trombley@blahblahblah","Kyle Trombley"),
IF(COUNTIF($C8,"*1083*"),HYPERLINK("mailto:scott.bennett@blahblahblah","Scott Bennett"),
IF(COUNTIF($C8,"*1084*"),HYPERLINK("mailto:scott.bennett@blahblahblah","Scott Bennett"),
IF(COUNTIF($C8,"*1085*"),HYPERLINK("mailto:davor.zubac@blahblahblah","Davor Zubac"),
IF(COUNTIF($C8,"*1086*"),HYPERLINK("mailto:davor.zubac@blahblahblah","Davor Zubac"),
IF(COUNTIF($C8,"*1087*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1089*"),HYPERLINK("mailto:nicholas.surma@blahblahblah","Nicholas Surma"),
IF(COUNTIF($C8,"*1090*"),HYPERLINK("mailto:kevin.demeere@blahblahblah","Kevin DeMeere"),
IF(COUNTIF($C8,"*1091*"),HYPERLINK("mailto:nicholas.surma@blahblahblah","Nicholas Surma"),
IF(COUNTIF($C8,"*1092*"),HYPERLINK("mailto:ali.taha@blahblahblah","Ali Taha"),
IF(COUNTIF($C8,"*1093*"),HYPERLINK("mailto:adam.goldberg@blahblahblah","Adam Goldberg"),
IF(COUNTIF($C8,"*1094*"),HYPERLINK("mailto:adam.goldberg@blahblahblah","Adam Goldberg"),
IF(COUNTIF($C8,"*1095*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1096*"),HYPERLINK("mailto:eric.henderson@blahblahblah","Eric Henderson"),
IF(COUNTIF($C8,"*1097*"),HYPERLINK("mailto:eric.henderson@blahblahblah","Eric Henderson"),
IF(COUNTIF($C8,"*1098M*"),HYPERLINK("mailto:nicholas.surma@blahblahblah","Nicholas Surma"),
IF(COUNTIF($C8,"*1099*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1100*"),HYPERLINK("mailto:brian.curry@blahblahblah","Brian Curry"),
IF(COUNTIF($C8,"*1101*"),HYPERLINK("mailto:wally.hedemark@blahblahblah","Wally Hedemark"),
IF(COUNTIF($C8,"*1102*"),HYPERLINK("mailto:garrett.vermeersch@blahblahblah","Garrett Vermeersch"),
IF(COUNTIF($C8,"*1103*"),HYPERLINK("mailto:jason.poag@blahblahblah","Jason Poag"),
IF(COUNTIF($C8,"*1104*"),HYPERLINK("mailto:adam.goldberg@blahblahblah","Adam Goldberg"),
IF(COUNTIF($C8,"*1105*"),HYPERLINK("mailto:davor.zubac@blahblahblah","Davor Zubac"),
IF(COUNTIF($C8,"*1106*"),HYPERLINK("mailto:timothy.clemens@blahblahblah","Tim Clemens"),
IF(COUNTIF($C8,"*1107*"),HYPERLINK("mailto:chadd.willson@blahblahblah","Chadd Willson"),
IF(COUNTIF($C8,"*1108*"),HYPERLINK("mailto:shaun.jaward@blahblahblah","Shaun Jaward"),
IF(COUNTIF($C8,"*1109M*"),HYPERLINK("mailto:shaun.jaward@blahblahblah","Shaun Jaward"),
IF(COUNTIF($C8,"*1110*"),HYPERLINK("mailto:eric.henderson@blahblahblah","Eric Henderson"),
IF(COUNTIF($C8,"*1111*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1112M*"),HYPERLINK("mailto:kyle.trombley@blahblahblah","Kyle Trombley"),
IF(COUNTIF($C8,"*1113*"),HYPERLINK("mailto:adam.goldberg@blahblahblah","Adam Goldberg"),
IF(COUNTIF($C8,"*1114M*"),HYPERLINK("mailto:eric.henderson@blahblahblah","Eric Henderson"),
IF(COUNTIF($C8,"*1115M*"),HYPERLINK("mailto:scott.bennett@blahblahblah","Scott Bennett"),
IF(COUNTIF($C8,"*1116*"),HYPERLINK("mailto:kirk.peffers@blahblahblah","Kirk Peffers"),
IF(COUNTIF($C8,"*1117*"),HYPERLINK("mailto:kyle.trombley@blahblahblah","Kyle Trombley"),
IF(COUNTIF($C8,"*1118*"),HYPERLINK("mailto:chadd.willson@blahblahblah","Chadd Willson"),
IF(COUNTIF($C8,"*1119*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1120*"),HYPERLINK("mailto:davor.zubac@blahblahblah","Davor Zubac"),
IF(COUNTIF($C8,"*1121*"),HYPERLINK("mailto:jamie.celestini@blahblahblah","Jamie Celestini"),
IF(COUNTIF($C8,"*1122M*"),HYPERLINK("mailto:timothy.clemens@blahblahblah","Tim Clemens"),
IF(COUNTIF($C8,"*1123*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1124*"),HYPERLINK("mailto:shaun.jaward@blahblahblah","Shaun Jaward"),
IF(COUNTIF($C8,"*1125*"),HYPERLINK("mailto:shaun.jaward@blahblahblah","Shaun Jaward"),
IF(COUNTIF($C8,"*1126*"),HYPERLINK("mailto:Jacob.Johnson@blahblahblah","Jacob Johnson"),
IF(COUNTIF($C8,"*1127*"),HYPERLINK("mailto:michael.mcgraw@blahblahblah","Mike McGraw"),
IF(COUNTIF($C8,"*1128*"),HYPERLINK("mailto:jamie.celestini@blahblahblah","Jamie Celestini"),
"Z"))))))))))))))))))))))))))))))))))))))))))))))))))
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Yeah create a lookup table. Then you can use something like:

=HYPERLINK(INDEX($B$1:$B$10,MATCH(1,INDEX(ISNUMBER(SEARCH($A$1:$A$10,E1))*($A$1:$A$10<>""),0),0)),INDEX($C$1:$C$10,MATCH(1,INDEX(ISNUMBER(SEARCH($A$1:$A$10,E1))*($A$1:$A$10<>""),0),0)))

For this the lookup table is in A1:C10
The lookup value is in E1
The formula is in F1

If you have 365 it can be simplified but you dont state.
 
Upvote 0
Hi JMPatrick,

I was asked to repost my table solution as it was deleted.

JMPatrick.xlsx
ABCDEFGHI
1CodeEmailName
21072mailto:chadd.willson@blahblahblahChadd Willson
31076mailto:michael.zaski@blahblahblahMike Zaski
41079mailto:scott.bennett@blahblahblahScott Bennett
51080mailto:brian.curry@blahblahblahBrian Curry
61081mailto:kyle.trombley@blahblahblahKyle Trombley
71083mailto:scott.bennett@blahblahblahScott Bennett
8Brian Curry10801084mailto:scott.bennett@blahblahblahScott Bennett
91085mailto:davor.zubac@blahblahblahDavor Zubac
101086mailto:davor.zubac@blahblahblahDavor Zubac
Sheet1 (2)
Cell Formulas
RangeFormula
A8A8=HYPERLINK(INDEX($H$2:$H$51,MATCH("*"&C8&"*",$G$2:$G$51,0)),INDEX($I$2:$I$51,MATCH("*"&C8&"*",$G$2:$G$51,0)))
 
Upvote 0
I do not know if it is me or XL2BB but the match part doesn't work with 2010 or 365.

The following does work
=HYPERLINK(INDEX($H$2:$H$51,MATCH(C8,$G$2:$G$51,0)),INDEX($I$2:$I$51,MATCH(C8,$G$2:$G$51,0)))
 
Upvote 0
Dave Patton said:
I do not know if it is me or XL2BB but the match part doesn't work with 2010 or 365.

The following does work
=HYPERLINK(INDEX($H$2:$H$51,MATCH(C8,$G$2:$G$51,0)),INDEX($I$2:$I$51,MATCH(C8,$G$2:$G$51,0)))
Click to expand...
Do you have the codes as numeric? We know the OP has them as text because some of the examples contain alpha characters.
 
Upvote 0
"Do you have the codes as numeric?" Yes

They imported as numbers.
 
Upvote 0
This is working for me:

VBA Code: 
=HYPERLINK(INDEX(D:D,MATCH($F$1,B:B,0)),INDEX(C:C,MATCH($F$1,B:B,0)))

I can't figure out how to copy and paste the formula down the sheet. When I do every row is the same. I'm expecting $F$1 in the first row, $F$2 in the second and so on, but I get $F$1 in every row.

What am I doing wrong?

Also, I can't figure out how to use the formula on another sheet. I need to reference the sheet with the table somehow but I'm not sure where in the formula.
 
Upvote 0
I'm not following as this doesn't look like the original question and I don't know what's in each column.

Let me give you an example of this data table

JMPatrick-V2.xlsx
ABCD
1CodeEmailName
21072mailto:chadd.willson@blahblahblahChadd Willson
31076mailto:michael.zaski@blahblahblahMike Zaski
41079mailto:scott.bennett@blahblahblahScott Bennett
51080mailto:brian.curry@blahblahblahBrian Curry
61081mailto:kyle.trombley@blahblahblahKyle Trombley
71083mailto:scott.bennett@blahblahblahScott Bennett
81084mailto:scott.bennett@blahblahblahScott Bennett
91085mailto:davor.zubac@blahblahblahDavor Zubac
101086mailto:davor.zubac@blahblahblahDavor Zubac
111087mailto:michael.zaski@blahblahblahMike Zaski
Data


...and this retrieval using the Code

JMPatrick-V2.xlsx
ABCDEF
1Mike Zaski1076
2Scott Bennett1079
3Brian Curry1080
4Eric Henderson1096
5Eric Henderson1097
6Nicholas Surma1098M
7Mike Zaski1099
8Shaun Jaward1108
9Shaun Jaward1109M
10Eric Henderson1110
11Mike Zaski1111
12#N/A
Retrieve
Cell Formulas
RangeFormula
A1:A12A1=HYPERLINK(INDEX(Data!C:C,MATCH($F1,Data!$B:$B,0)),INDEX(Data!D:D,MATCH($F1,Data!$B:$B,0)))


How close am I?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top