How to lookup to return an active hyperlink in Excel?
In day-to-day work with Excel, it’s common to use the VLOOKUP function to find and return values associated with specific entries in your data. However, a frequent problem arises when your lookup returns hyperlinks: instead of clickable, active links, you only get plain text URLs. For example, if your source data contains a website link with an embedded hyperlink, after a typical VLOOKUP, the result will only display as unformatted text, as illustrated below.
In such cases, ensuring that the lookup returns an active hyperlink—one you can click and that opens in your browser—improves usability, saves time, and is important for datasets involving web addresses, file paths, or other clickable resources.
This tutorial introduces multiple practical solutions for returning active hyperlinks through lookups, analyzing their scenarios, applicable data types, and potential limitations. You’ll also learn key precautions, troubleshooting tips, and recommendations for choosing the best method to fit your worksheet needs.
- Lookup to return an active hyperlink with formula
- VBA code – Return and insert an active hyperlink via lookup (advanced scenarios)
Lookup to return an active hyperlink with formula
To look up and return a value as an active hyperlink, you can combine the HYPERLINK and VLOOKUP functions. This approach is simple and suitable for source data where hyperlinks are cleanly stored as text URL addresses (such as “https://www.example.com” or network file paths). This will make the returned value clickable in your worksheet.
Suppose you have a table with two columns: one for the lookup value (such as a name) and one for a URL as plain text or hyperlink. To retrieve the corresponding active hyperlink based on a user input value, follow these steps:
1. Enter the following formula into a blank cell where you want to display the result:
=HYPERLINK(VLOOKUP(D2, $A$1:$B$8,2, FALSE))2. Press Enter to confirm. The cell now displays the hyperlink as an active, clickable link, as shown below:
Parameter and usage notes:
- D2: The cell containing the value you want to look up.
- $A$1:$B$8: The data range where the first column contains lookup values and the second contains hyperlinks. Use absolute references if you plan to copy the formula.
- 2: Indicates that the hyperlink resides in the second column of your range.
Tips:
- If your lookup value is not found, the formula will return an error (#N/A). Double-check that your lookup values have an exact match in the table range.
- If you want the displayed text to differ from the actual hyperlink (for example, display a name instead of the URL), you can add an optional second parameter to HYPERLINK:
=HYPERLINK(VLOOKUP(D2,$A$1:$B$8,2,FALSE),D2)
This displays the value of D2 as the link text. - This approach works only when hyperlinks are stored as standard URL or file path text. It does not recover Excel-inserted hyperlinks where the display text and hyperlink address are different, or “friendly” display names without a raw URL in the cell.
Common issues and troubleshooting:
- If the result is not clickable, ensure your data contains a full valid web URL (including "http://" or "https://").
- If you get incorrect or missing results, check your lookup range and ensure the column index matches the column containing hyperlinks.
- For local files, ensure your hyperlink path uses the appropriate format (e.g., "C:\Folder\file.xlsx").
Advantages: Simple to set up, formula can be dragged to apply to multiple rows, best for tables where hyperlinks are stored as plain text URIs.
Limitations: Does not support retrieving display text and hyperlink address separately if they differ, nor recognizes manually-inserted hyperlinks where only display text is visible in the cell.
VBA code – Return and insert an active hyperlink via lookup (advanced scenarios)
If you need to look up a specific value in a range and return its original active hyperlink—including both the displayed text and the clickable link itself—rather than just the plain URL, VBA provides a reliable solution. This code searches for the target value in your selected range, and when a match is found, it copies the exact hyperlink (text + address) into the cell you specify. This is especially useful when the displayed text and hyperlink address differ, or when formula-based methods cannot capture the actual link.
This method is particularly useful when your data includes “friendly” display names with underlying hyperlinks, hyperlinks to files or folders, or non-standard hyperlink formats. VBA enables you to copy over both the visible link text and the underlying hyperlink address, or reinsert a hyperlink in a new location using a lookup result.
Precautions: Ensure macros are enabled in your Excel environment. Always back up your workbook before running VBA scripts, especially if you’re working with important data.
Advantages: Handles complex cases—such as cell-inserted hyperlinks and separation of display text and hyperlink address. Allows you to process batches of hyperlinks or customize results.
Limitations: Requires basic VBA familiarity, and not supported in all restricted or web-based Excel environments.
1. Open the VBA editor by clicking Developer > Visual Basic. In the newly opened window, click Insert > Module, and paste the following code into the module:
Sub LookupAndInsertHyperlink()
Dim LookupValue As String
Dim LookupRange As Range
Dim ResultCell As Range
Dim cell As Range
Dim hyperlinkFound As Boolean
Dim linkAddress As String
Dim linkText As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set LookupRange = Application.InputBox("Select the lookup range (must include display text/cell and hyperlink)", xTitleId, Selection.Address, Type:=8)
Set ResultCell = Application.InputBox("Select the cell to output the hyperlink", xTitleId, "", Type:=8)
LookupValue = Application.InputBox("Enter the value to lookup", xTitleId, "", Type:=2)
hyperlinkFound = False
For Each cell In LookupRange
If cell.Value = LookupValue Then
If cell.Hyperlinks.Count > 0 Then
linkAddress = cell.Hyperlinks(1).Address
linkText = cell.Value
ResultCell.Hyperlinks.Add Anchor:=ResultCell, Address:=linkAddress, TextToDisplay:=linkText
hyperlinkFound = True
Exit For
End If
End If
Next
If Not hyperlinkFound Then
ResultCell.Value = "No matching hyperlink found"
End If
End Sub2. To run the script, with your workbook open, press Alt + F8, select LookupAndInsertHyperlink, and click Run.
3. In the dialogs that appear:
- Select your lookup data range (including both the values and their hyperlinks).
- Choose the target cell for the output hyperlink.
- Input the lookup value you want to search for. The macro will find the matching value, extract its hyperlink (even if the display text is different from the underlying link) and insert it as an active hyperlink at your chosen location.
Practical tips and error reminders:
- If the value is not found or no hyperlink exists in the cell, the target will show “No matching hyperlink found.”
- If you want to process multiple lookups at once, consider extending the VBA code or running the script multiple times as needed.
- This script is effective even when the hyperlink is not simply a URL string in the cell, and will also copy both address and display text.
Troubleshooting suggestions:
- Verify your input range includes the column with the actual hyperlinks.
- If the VBA macro doesn't run, check that macros are enabled in your Excel settings.
- If “No matching hyperlink found” appears, double-check your lookup value is correct and that corresponding hyperlinks exist in that row.
- Always save your workbook before running macros in case you need to undo changes.
Summary:
- Use the formula method for standard, text-based hyperlinks and quick lookups.
- Use the VBA method for more advanced needs—such as restoring manually-inserted hyperlinks, retrieving both display text and link address, or applying results across ranges dynamically.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in