First, I would think that using a separate cell to hold your comments would make
your live much easier.  You could just use =vlookup() or =index(match()) to
return the text.

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html  (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html  (for =index(match()))

But...

One of the "features/bugs" of excel is that you can return comments using a user
defined function.

Actually, you could use a UDF to retrieve the value and comment:

Option Explicit
Function VlookupComment(myVal As Variant, myTable As Range, _
                           myColumn As Long, myBoolean As Boolean) As Variant

   Application.Volatile True
   
   Dim res As Variant 'could be an error
   Dim myLookupCell As Range

   With Application.Caller
       If .Comment Is Nothing Then
          'do nothing
       Else
          .Comment.Delete
       End If
   end with
   
   res = Application.Match(myVal, myTable.Columns(1), myBoolean)
   If IsError(res) Then
       VlookupComment = "Not Found"
   Else
       Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
       VlookupComment = myLookupCell.Value
       With Application.Caller
           If myLookupCell.Comment Is Nothing Then
               'no comment, do nothing
           Else
               .AddComment Text:=myLookupCell.Comment.Text
           End If
       End With
   End If

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like:   VBAProject (yourfilename.xls)  

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=VlookupComment(a1, 'sheet2'!a:e, 5, false)