How to vlookup and return matching value with cell comment?

When you apply the Vlookup function to return the matching value, it will only extract the value without the formatting, such as fill color, font or comment etc. But, sometimes, you may need to vlookup and return the matched value including comment as well to get the following screenshot result. How could you solve this job in Excel?

Vlookup and return matching value with cell comment by using VBA code

Vlookup and return matching value with cell comment by using VBA code

The below VBA code may help you to vlookup and return matched value with its comment, please do as follows:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, then copy and paste the following code in the Module Window.

VBA code: Vlookup and return matching value with cell comment:

Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
        VlookupComment = "Not Found"
    Else
        Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
        VlookupComment = xCell.Value
        With Application.Caller
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            If Not xCell.Comment Is Nothing Then
                .AddComment xCell.Comment.Text
            End If
        End With
    End If
End Function

3. And then save the code and close the code window, enter this formula: =vlookupcomment(H2,A2:C10,3,FALSE) into a blank cell to locate the result, and press Enter key, the matched value as well as the comment is returned at once, see screenshot:

Note: In the above formula, H2 is the lookup value you want to return its corresponding value, A2:C10 is the data table you want to use, the number 3 is the column number which contains the matched value you want to return.

Unlock Excel Magic with Kutools AI

• Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
• Custom Formulas: Generate tailored formulas to streamline your workflows.
• VBA Coding: Write and implement VBA code effortlessly.
• Formula Interpretation: Understand complex formulas with ease.
• Text Translation: Break language barriers within your spreadsheets.

Enhance your Excel capabilities with AI-powered tools. Download Now and experience efficiency like never before!