Let's assume that by using some combination of MATCH / INDEX / etc. we can
get the Address of the cell. So then some cell, say Z100 displays B1 as a
result of the worksheet formula.

=INDIRECT(Z100) will then display the Value of B1
=coment(Z100) will then display the comment in B1.

The UDF is:

Function coment(s As String) As String
coment = ""
If Range(s).Comment Is Nothing Then Exit Function
coment = Range(s).Comment.Text
End Function
--
Gary''s Student - gsnu200765


"Lucy" wrote:

I'm looking for creating a user defined function. Will macros help?

"Gary''s Student" wrote:

The INDEX function can retrun the value of a cell. It cannot return the
associated comment in the cell or the format of that cell.
--
Gary''s Student - gsnu200765


"Lucy" wrote:

Hi there,

A B
1 Apple 44 (with red-arrow comment "fruit")
2 Pine 55
3 Apple 66
4 Orange 77

I've added comment "fruit" on cell B1
After I use INDEX function to locate B1, only the value in the cell shows
but NOT
the comments. Which means, only "44" shows.

How can I show the red-arrow comment "fruit" as well?

Thanks.