# Thread: Vlookup array formula possible? (EXCEL XP)

1. ## 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:
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. ## 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. ## 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. ## 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>

5. ## 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. ## Re: Vlookup array formula possible? (EXCEL XP)

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

Steve

7. ## 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. ## 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
•