vlookup including the comment (excel 97)

Topic

New Reply

how to use vlookup function to see the comment in that cell also.
thanks a lot

Reply | Quote

Replies

Vlookup can no see cell Comments, and as far as I know, none of the worksheet functions can.

Reply | Quote

Legare Coleman
thank you for the promptly reply.

Reply | Quote

Only using a user defined function:

Function VlookupWithComment(rLookForRange As Range, rLookInRange As Range, lColumnOffset As Long, bIncludeComment As Boolean)
Dim vTemp As Variant
Dim oSheet As Object
Dim rFoundcell As Range
Set oSheet = Application.Caller.Parent
Set rFoundcell = rLookInRange.Cells(Application.WorksheetFunction.Match(rLookForRange.Value, rLookInRange.Columns(1), 0), lColumnOffset)
vTemp = rFoundcell.Value
If bIncludeComment Then
vTemp = vTemp & “, Comment: ” & rFoundcell.Comment.Text
End If
VlookupWithComment = vTemp
End Function

Paste this VBA code into a normal module and call the function from your worksheet like this:

=VlookupWithComment(E2,A1:B5,2,TRUE)

Reply | Quote

Pieterse
I am new on user defined fuction. When I copy and save your code into the module, I do not know how to retrive it to edit.
Joe

Reply | Quote

You press alt-F11 to get into the Visual Basic Editor. Then use the “Project explorer” (normally on the left side of the VBE’s window) to navigate to the module that you pasted the code into.

One remakr: My user defined function yields a string result. If you want to do calculations with the found matches, you’ll need to extract the number from the string.

Reply | Quote