Hi Pran
There is no built-in functionality to do that. You can try the below UDF
(User Defined function).
Syntax:
=VLOOKUP_COMMENT(strLookupValue,rngLookUpArray,intColumn)
rngLookUpArray is the Lookuprange
strLookupValue is the lookup string or cell reference
intColumn is the column to be concatenated
Examples:
'1. To vlookup 1 in col A and return value from Column C. If comments are
present for Col C the function will return the comments to the formula cell.
=VLOOKUP_COMMENT(1,A:C,3)
OR
'with the lookup value in cell D1
=VLOOKUP_COMMENT(D1,A:C,3)
Below is the code to be pasted to the code module. From workbook launch VBE
using Alt+F11. From menu Insert a Module and paste the below function.Close
and get back to workbook and try the formula.
Function VLOOKUP_COMMENT(strLookupValue As String, _
rngLookUpArray As Range, intColumn As Integer)
Dim lngRow As Long, rngTemp As Range
For lngRow = 1 To rngLookUpArray.Rows.Count
If CStr(rngLookUpArray(lngRow, 1)) = strLookupValue Then _
VLOOKUP_COMMENT = rngLookUpArray(lngRow, intColumn): Exit For
Next
Set rngTemp = Application.Caller
If Not rngTemp.Comment Is Nothing Then rngTemp.Comment.Delete
If Not rngLookUpArray(lngRow, intColumn).Comment Is Nothing Then
rngTemp.AddComment rngLookUpArray(lngRow, intColumn).Comment.Text
End If
End Function
If this post helps click Yes
