Hyperlink & Vlookup formula

kywenyi

New Member
Joined
May 11, 2019
Messages
11
hi, i have 1 workbook with 2 tabs - on Sheet 1 i need to hyperlink the column COLOR to Sheet 2 with vlookup function.

i've tried this but doesn't work. =HYPERLINK(VLOOKUP(B2,'SHEET 2'!A1:B3,2,0)

please help me with this. i will need to key in the formula in Sheet 1 B2, and when click on B2, it brings me to Sheet 2 A2, and in Sheet 1 B2, it will display as Sheet 2 A2 name.

Sheet1

AB
1FRUITCOLOR
2APPLERED
3LEMONYELLOW

<tbody>
</tbody>







Sheet 2

A
B
1
COLORDETAILS
2
REDSWEET
3
YELLOWSOUR

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

kurtisw

New Member
Joined
Jun 16, 2019
Messages
7
Since you are attempting to reference the same cell that you are wanting to place the formula in you will need to reference the actual text value you are searching for.

Place the following formula in cell B2 on Sheet 1
=HYPERLINK("#"&CELL("address",INDEX(Sheet2!$A1:$B3,MATCH("Red",Sheet2!$A:$A,0),0)),"Red")

If you want to use a cell reference you will need to place the formula in a cell other than the one you are referencing.
 

kywenyi

New Member
Joined
May 11, 2019
Messages
11
i cannot do that. i have a full list of data and they are not in fixed row on sheet 2. previously my colleague use the normal hyperlink but i realise whenever i add a data in sheet 2, my hyperlink all wrong due to insert of row. thus, i need hyperink with vlookup. anything you can suggest?
 

kurtisw

New Member
Joined
Jun 16, 2019
Messages
7
Can you place this formula in C2?

=HYPERLINK("#"&CELL("address",INDEX(Sheet2!$A1:$B3,MATCH($B2,Sheet2!$A:$A,0),0)),$B2)
 

kywenyi

New Member
Joined
May 11, 2019
Messages
11
i tried but can't. it shows NA.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,714
Messages
5,488,478
Members
407,639
Latest member
Carlos Ottoni

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top