Results 1 to 3 of 3

Thread: VLOOKUP (2007)

  1. #1
    New Lounger
    Join Date
    Jan 2007
    Melbourne, Australia
    Thanked 0 Times in 0 Posts

    VLOOKUP (2007)

    Hello I need help with the following vlookup. I have attached a workbook with a sample of what I need to do. If someone could look at it and give me advise I would appreciate it very much.
    In the "Filling" worksheet I enter a code into column B and do a vlookup into column C from the "Items" worksheet column B. Could anyone tell me how to do the following:
    Stop getting the #N/A error in empty rows?
    Bring over the comments tag that is attached to the description (Items sheet column .
    Attached Files Attached Files

  2. #2
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 465 Times in 382 Posts

    Re: VLOOKUP (2007)

    Hi chilz,

    In row 24, where you've got the unwanted 'N/A' result, add an IF test to the formula, thus:
    =IF(ISERROR(VLOOKUP(B24,'Items Sheet'!$1:$65536,2,FALSE)),"",VLOOKUP(B24,'Items Sheet'!$1:$65536,2,FALSE))
    This should suppress the output. If you'd prefer some other utput, add it between the double quotes in the formula. Copy & paste the formula to your other cells.

    You can't retrieve a cell's comments via a formula. You could use vba, though, but that would raise the usual macro security issues.

    Paul Edstein
    [MS MVP - Word]

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: VLOOKUP (2007)

    If you choose the VBA route, you can use this function to get the comment.

    <pre>Option Explicit
    Function GetComment(vValue, rLookup As Range, iCol As Integer)
    Dim lRow As Long
    Dim sCmt As String
    lRow = 0
    On Error Resume Next
    lRow = Application.WorksheetFunction.Match(vValue, rLookup.Columns(1), 0)
    On Error GoTo 0
    sCmt = "" 'No Match
    If lRow <> 0 Then
    sCmt = "No Comment" 'No Comment
    On Error Resume Next
    sCmt = rLookup.Cells(lRow, iCol).Comment.Text
    On Error GoTo 0
    End If
    GetComment = sCmt
    End Function</pre>

    Use in a cell something like
    =getcomment(B5,'Items Sheet'!$1:$65536,2)

    If no match is found, it returns a null string, if no comment is in the cell, then it returns "No Comment".


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts