VLOOKUP function including the link in the to be copied cell RRS feed

  • Question

  • Dear,

    Is it possible to use in Excel the VLOOKUP function including copying the link in the specific cell additional to the visible content of the cell? If yes how? The result should be the visible content including the link in the new cell



    Sunday, June 6, 2021 5:50 PM

All replies

  • Are you trying to perform an arithmetic operation on the value in the cell and the value returned by VLOOKUP?  If so, and using addition as an example, let the current contents of the cell be expressed as formulla_1: change the contents to be formula_1 + vlookup(value,array,index,match).  If necessary, value could also be a repeat of formula_1.

    If you are trying to concatenate text, change the cell contents to formula_1 & vlookup(...).

    Monday, June 7, 2021 1:35 AM
  • Let's say your want to look for the content of A1 in the cells A2 to A100. If what you look for is found, you want to show what you were looking for and what is the correponding B column cell. In that case, I would go for the formula

    =CONCAT(A1,VLOOKUP(A1,A2:E100, 2, TRUE))

    Note: Make sure the table is ordered on the A column before you use the formula. Using TRUE on the last parameter will mean it is an approximate match (if you want an exact one, go for FALSE)



    Technical Advisor Connecting Software |

    If this post answers the question, please click "Mark as answer"/"Propose as answer" on the post and "Mark as helpful"

    Thursday, June 17, 2021 1:57 PM
  • Are you trying to extract the URL from a hyperlink in Excel?

    If yes,

    1. Open up a new workbook.
    2. Get into VBA (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste the Excel user defined function below
    5. Press F5 and click “Run”
    6. Get out of VBA (Press Alt+Q)

    Sub ExtractHL()
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
    HL.Range.Offset(0, 1).Value = HL.Address
    End Sub

    This will separate all the URLs from the hyperlinks in the adjacent cell. 

    Wednesday, July 7, 2021 11:42 PM