Syntactically, EXCEL does accept VLOOKUP in an array formula.  However, I can not get it to produce the correct result.  Any assistance would be appreciated.

I wish to sum the result of a series of VLOOKUPs, for a set of lookup values.  The second and third VLOOKUP arguments remain constant for the VLOOKUP series.  That is, just plain vanilla VLOOKUP, not some multi-criteria scenario.

I currently use a set of cells with VLOOKUPS, one per lookup value, then sum the set of cells.  I wish to condense this to the single cell form:

{=SUM(VLOOKUP(ValueSet, TableArray, IndexNum))}

Where ValueSet is a one-dimensional range of cells.  Note: TableArray is an INDIRECT() cell reference in my project.  IndexNum is a constant.

Every variation that I have thought to try performs the VLOOKUP using only the first value of ValueSet.  It will not perform the VLOOKUP, and add the result to the sum, for all subsequent lookup values in ValueSet.

Is the built-in function VLOOKUP incompatible with array function usage?  Perhaps due to the required array in its second argument?

ETA: I am using Excel 2010

• Edited by MikeInFairfax 13 hours 33 minutes ago
• Moved by Lanlan HuangMicrosoft contingent staff 1 hour 51 minutes ago

I suspect that you'll have to keep on using the cells with intermediate results.

In your formula, you've omitted the fourth argument (ie. RangeLookup) in VLOOKUP.  Therefore, if an exact match isn't found, and approximate match is returned.  If this is indeed your intent, try...

=SUMPRODUCT(LOOKUP(ValueSet,INDEX(TableArray,0,1),INDEX(TableArray,0,IndexNum)))

Otherwise, for an exact match, try...

=SUMPRODUCT(SUMIF(INDEX(TableArray,0,1),ValueSet,INDEX(TableArray,0,IndexNum)))

Hope this helps!

Thanks for the assistance!

Hi

This is the forum to discuss questions about Microsoft Excel (VSTO, VBA) develop. For your question is about formula in Excel, I will move this question to forum for Excel

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thank you for your understanding.

Best Regards,

Lan