Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Vlookup array formula possible? (EXCEL XP)

    Pls. be patient with me on this post as I am having difficulty conceptualizing what I need and how to communicate it to the "experts".

    I have a column (say column A) of numbers [ there are blank cells in the column also]. These numbers are being used in a vlookup formula to snatch a number on another sheet . My current solution is to have a vlookup formula in another column (column [img]/forums/images/smilies/cool.gif[/img] for all rows in the named range in column A. I then sum the data returned by the vlookup formula in column B. This works, but my ultimate application will need 3500-5000 cells to make this approach work.

    What I am looking for is a formula that for each cell in the range in column A, performs the vlookup [if applicable--remember there are blank cells in this range] and then sums the total of the lookup amounts. There will also be values in column A that are not in the lookup table, so I need to avoid the #n/a error message as well.
    Simple example:
    Column A (downloaded for server) Column B (result of vlookup formula)
    330 455.65
    331 #n/a
    blank
    445 300.00

    I am looking for a single cell formula that returns 755.65 in the example above (the sum of the vlookup values in column [img]/forums/images/smilies/cool.gif[/img].
    Is this possible? THANKS.

  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 array formula possible? (EXCEL XP)

    I do not think it is possible to do this without the intermediate column (unless you created your own function).

    To have the intermediate calcs ignore the blanks the items not in the list you can use something like:
    =IF(ISNUMBER(VLOOKUP(A1,$D$1:$E$200,2,0)),VLOOKUP( A1,$D$1:$E$200,2,0),0)

    Change this as appropriate for your ranges. Copy this down the column and then sum them together.

    Steve

  3. #3
    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 array formula possible? (EXCEL XP)

    Here is a UDF I alluded to.

    <pre>Option Explicit
    Function SumVLookup(rValue As Range, rLookup As Range, iColumn As Integer)
    Dim rCell As Range
    Dim dValue As Double
    Dim dSum As Double
    Dim AWF As WorksheetFunction
    If iColumn > rLookup.Columns.Count Then
    SumVLookup = CVErr(xlErrNum)
    Exit Function
    End If
    Set AWF = Application.WorksheetFunction
    dSum = 0
    For Each rCell In rValue
    dValue = 0
    On Error Resume Next
    dValue = AWF.VLookup(rCell.Value, rLookup, iColumn, 0)
    On Error GoTo 0
    If dValue <> 0 Then
    dSum = dSum + dValue
    End If
    Next
    SumVLookup = dSum
    End Function</pre>


    Add it to a normal module and call it like:
    =SUMVLOOKUP(A1:A5,$D$1:$E$200,2)

    It will vlookup each item from A1:A5, and if it is not an error, it will sum them all together. This does not need the intermediate column.

    Steve

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup array formula possible? (EXCEL XP)

    You did not say what you wanted in column B if the value in column A is not in the table. The following formula can be put in cell B1 and copied down as far as needed. You will need to change the Sheet2!$A$1:$B$1000 to specify your actual lookup table. This formula will leave column B blank if column A is blank and will put the message "Not in table" in column B if the value is not found in the table. You can now sum column B using the SUM function which will ignore blanks and non numeric cells.

    <pre>=IF(TRIM(A1)="","",IF(ISERROR(VLOOKUP(A1,Shee t2!$A$1:$B$1000,2,FALSE)),"Not in Table",VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE)))
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Vlookup array formula possible? (EXCEL XP)

    Steve,
    Thanks it does what I wanted. I did have to change the function name to MySumVLookup as I received a dialog box when I executed the SumVLookup function as follows: Ambiguous name detected: sumvlookup. I am far from an expert, but thought that there must already be an EXCEL function named "sumvlookup". When I changed the three references in the code from the "sumvlookup" to "mysumvlookup" it worked like a charm. I must say that I'll need some time to digest this function...again thank you. Jim

  6. #6
    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 array formula possible? (EXCEL XP)

    Glad I could help.

    There is no sumvlookup in XL97 and I had no issues with it. Perhaps Excel XP is different...

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup array formula possible? (EXCEL XP)

    There is no sumvlookup in standard Excel 2002 (XP) either. My guess is that Jim accidentally pasted the function twice.

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Vlookup array formula possible? (EXCEL XP)

    Hans,
    Good guess. I was interrupted while working through the process yesterday and now that I have gone back into VBA, I found a Module1 with the same code. So I must have posted the code, been interrupted and posted it again into Module2. I deleted Module1 and rename the function back to Steve's original post and it works. I wanted to post this information to the thread so that someone else knows that the code is fine in Excel XP too. THANKS.

Posting Permissions

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