Hyperlink with vlookup formula not working

[SugarPrincess21]

Okay I give up I have tried this multiple ways and both ways my formula shows up correctly however its not taking me to the cell I am requesting. I am really new at excel so I apologize if I don't make sense thank you in advance I really appreciate the help.


=HYPERLINK(VLOOKUP(AW5,JobNumber,1,0),"Go To")

JobNumber refers to "Tank Jobs" sheet column "C"

I also had it written as

=HYPERLINK(VLOOKUP(AW5,'Jobs'!$C$C,3,FALSE),"Go To") but it would only search 'A' Column and I didn't want to move Job number column to 'A' so I defined the column in name manager.

The reason I am sure my formula is correct as my Friendly name "Go to" shows up it and does not return any error such as #N/A or #REF . However when I click on the go to link it says...

"unable to open https://.......................................The internet site reports that the item you requested could not be found. (HTTP/1.0 404)

What I am trying to do is link Job numbers on one sheet to a more detailed sheet of the job since there will be over 500 plus job numbers by the end of the year it would be nice to not have to link them individually each time and to just have to type the job number and copy down the formula to the next cell for the next job number. I don't want to use the

find function it would be nice to just click and ta da! I'm at the correct cell. ​

Since the Tank jobs spreadsheet is going to vary on how information is sorted it would also be nice if the hyperlink adjusted automatically by using vlookup function.

Thank you again in advance for your help on this. I am using Windows 10pro and the most recent version of excel I believe.
​

 

[tyija1995]

Are the values in column C legit website links?

Also where you wrote
=HYPERLINK(VLOOKUP(AW5,'Jobs'!$C:$C,3,FALSE),"Go To")
this looks illegal --> You are looking for the 3rd column when only spanning 1 in the table array parameter.

Can you give an example of one of the values in column C?

 

[FormR]

What I am trying to do is link Job numbers on one sheet to a more detailed sheet of the job

Hi, it sounds like you want something like this:

=HYPERLINK("#'Tank Jobs'!C"&MATCH(AW5,'Tank Jobs'!C:C,0),"Go to")

 

[SugarPrincess21]

Are the values in column C legit website links?

Also where you wrote
=HYPERLINK(VLOOKUP(AW5,'Jobs'!$C:$C,3,FALSE),"Go To")
this looks illegal --> You are looking for the 3rd column when only spanning 1 in the table array parameter.

Can you give an example of one of the values in column C?

I found that because I defined column C as Job Numbers I only needed to enter 1 in the table array parameter

 

[SugarPrincess21]

Hi, it sounds like you want something like this:

=HYPERLINK("#'Tank Jobs'!C"&MATCH(AW5,'Tank Jobs'!C:C,0),"Go to")

The "#' helped a bit it is now at least not trying to open up internet explorer it is trying to find the location in my excel file but I am now getting a new error

The syntax of this name isn't correct.
Verify that the name:
-Starts with a letter or underscore (_)
-Doesn't include a space or character that isn't allowed.
-Doesn't conflict with and existing name in the workbook.

This is what my code looks like now: =HYPERLINK("#'"&VLOOKUP(AT14,JobNumber,1,FALSE),"Go To")

This is what is in cell AT14 "HES163US"

I tried replacing my formula with this one:

=HYPERLINK("#'"&VLOOKUP("HES163US",JobNumber,1,FALSE),"Go To")​

and deleted HES163US out of every other location in the spreadsheet except the cell I'm trying to get it to link to it returned my "Go To" text in the cell but when I clicked on it I got the same error...

The syntax of this name isn't correct.....​

 

[FormR]

This is what my code looks like now: =HYPERLINK("#'"&VLOOKUP(AT14,JobNumber,1,FALSE),"Go To")

Hi, here is my understanding on what you are trying to do.

You have a "job number" in cell AT14 and that same job number exists in column C on a sheet called "Tank Jobs". You want to create a hyperlink that when clicked will go to the cell in column C of the "Tank Jobs" sheet that contains the job number in cell AT14.

If this is correct then stop trying to incorporate a VLOOKUP() function - it is not appropriate here, instead try the formula I suggested in post #3 - here it is again adjusted for the new cell reference.

=HYPERLINK("#'Tank Jobs'!C"&MATCH(AT14,'Tank Jobs'!C:C,0),"Go to")

If my understanding is not correct then try to describe in simple terms what your set-up is and what you are trying to do.

 

[SugarPrincess21]

Thank you so much, I must have had an error in copying over your formula the first time the other day. I decided to try it again just now because yes that is exactly what I am trying to accomplish and it works flawlessly. Much appreciated!