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 Sunday, July 19, 2015 5:59 PM
• Moved by Lanlan HuangMicrosoft contingent staff Monday, July 20, 2015 5:41 AM