Vertical Lookup to post the attached comment with the text

  • forum.ozgrid.com/index.php?attachment/48028/forum.ozgrid.com/index.php?attachment/48027/I 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)

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


    then in C2 do :
    =MyComment(INDIRECT(ADDRESS(21+MATCH(B2;B22:B27;0);2)))


    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!