INDEX MATCH to also pull through notes?

Question

I'm looking up to a tab of data 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 months 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 be able to get this to work, but to be frank my knowledge of VBA is 2 parts of sod all.

Is this something that is possible to achieve? How would one go about making this magic occur?

I've not tried anything because I know it's not possible with standard INDEX MATCH functions, so am at a bit of a loss as it will be necessary for this new report. I'd like to avoid having to add specific cells for commentary as it would make the summary unwieldy.

Many thanks in advance for your time.

Kind regards,

Luke

Answer 1

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.