Vlookup array formula possible? (EXCEL XP)

Topic

New Reply

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 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 .
Is this possible? THANKS.

Reply | Quote

Replies

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

Reply | Quote

Here is a UDF I alluded to.

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

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

Reply | Quote

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

Reply | Quote

Glad I could help.

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

Steve

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

=IF(TRIM(A1)="","",IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE)),"Not in Table",VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE)))

Reply | Quote