[SOLVED] INDEX MATCH to also pull through notes

INDEX MATCH to also pull through notes

I'm looking up a tab that has data ranging back for a couple of years. Much of the data also has notes attached to the cells.

In my summary tab, which will be looking up a month's worth of daily data, I need the notes attached to the source cells to also pull through when using the INDEX MATCH functions.

I know this is not standard behaviour for INDEX MATCH, and that a user defined function in VBA might work.

I'd like to avoid adding specific cells for commentary as it would make the summary unwieldy.

Solution

EDIT: this will pull the cell content and the comment from the referenced cell, and replicate them on the calling cell. I'm a little surprised this works with a UDF, since typically those are quite restricted in what they can do to the Excel environment, but I guess in this case it works...

Function GetNotes(v, matchRange, notesColLetter As String) As String
    Dim m, c As Range
    m = Application.Match(v, matchRange, 0) 'check for match
    If Not IsError(m) Then                  'got match
        With matchRange.Cells(m).EntireRow.Columns(notesColLetter) 'comments cell...
            GetNotes = .Value
            Set c = Application.ThisCell 'the cell with the formula
            If Not c.Comment Is Nothing Then c.Comment.Delete
            If Not .Comment Is Nothing Then c.AddComment Text:=.Comment.Text
        End With
    End If
End Function

Usage:

=GetNotes("blah",A:A,"C")

looks up "blah" in colA and returns the value and any note in colC on the matched row.