Vertical Lookup to post the attached comment with the text

  • post hours at ramdom into the spreadsheet by EMP NBR and HOURS.

    THere are times when I wish to identify a person associated with unusual hours.

    (see attached) In J5 the hours are wrong and I would like to Identify the person.
    I tried in cell C2 to do a vertical lookup hoping to bring forward the comment, but it only brings the actual value from the table.

    Is there a way to bring the comments with the value from a cell?

  • Re: Vertical Lookup to post the attached comment with the text

    First of all - the names are next to the numbers, so why don't you just do a vlookup and get the value from the next column ?

    =VLOOKUP(B2;names;2) or =VLOOKUP(B2;B22:C27; 2) in your case.

    assuming however, that in your real life sheet/data it will not be so nicely connected, in which case I urge you to take a look at the following :

    Put the following user defined function in a module (VBA editor -> insert -> module)

    Function MyComment(rng As Range)
        Dim str As String
        str = Trim(rng.Comment.Text)
        str = Application.Substitute(str, vbLf, " ")
        MyComment = str
    End Function

    then in C2 do :

    The "21" before match comes from the fact that you start looking in B22 - if it's anywhere else, make sure this "21" is the right amount between 1 and the row you start looking with match. The 2 at the end determines the column - column B = 2, column C = 3 etc.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!