Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLookup matching 2 items (excel2003)

    Hello All,
    You guys never failed me, and I'm hoping for some serious help again! :-)
    I have an excel sheet that I need to compare an order ID and an item number to find a status on another tab for that order Id, that Item number and to give me comments on it. I've attached a sheet that MUCh better explains.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLookup matching 2 items (excel2003)

    1) Add the attached function into a normal module
    2) add this formula to C2:
    <pre> =vlindex(B2,Data!$B$2:$B$7,1,COUNTIF($B$1:B2,B2))</pre>

    3) copy/autofill c2 to c3:C7

    For more info (and some related functions) see Re: Lookup more than one row (2000)

    Steve

    <pre>Option Explicit
    Function VLIndex(vValue, rngAll As Range, iCol As Integer, lIndex As Long)
    Dim x As Long
    Dim lCount As Long
    Dim vArray() As Variant
    Dim rng As Range
    On Error GoTo errhandler

    Set rng = Intersect(rngAll, rngAll.Columns(1))
    ReDim vArray(1 To rng.Rows.Count)
    lCount = 0
    For x = 1 To rng.Rows.Count
    If rng.Cells(x).Value = vValue Then
    lCount = lCount + 1
    vArray(lCount) = rng.Cells(x).Offset(0, iCol).Value
    End If
    Next x

    ReDim Preserve vArray(1 To lCount)
    If lCount = 0 Then
    VLIndex = CVErr(xlErrNA)
    ElseIf lIndex > lCount Then
    VLIndex = CVErr(xlErrNum)
    Else
    VLIndex = vArray(lIndex)
    End If
    errhandler:
    If Err.Number <> 0 Then VLIndex = CVErr(xlErrValue)
    End Function</pre>


Posting Permissions

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