Array formula error with VLOOKUP

Question

Hi,

I have encountered an error with an array function that I can't explain.  Basically, the formula that gives the wrong answer is:

=Sum(if(a2:a10=1,1,0)*vlookup(b2:b10, sheet!a1:c3, 3, false))

What seems to be happening is that I get the sum of the IF function and the array that the IF function returns is not multiplied by the array that the VLOOKUP function returns.

If instead of putting this into a single cell I have a column that has the IF array, a column that has the VLOOKUP array, a column that has the product of the two, and a cell that sums the product I get the correct answer.

So, why doesn't it all work in one cell?

Eric

Answer 1

Your formula, as written, will not return the results you want.

Try this:

=SUMPRODUCT((A2:A10=1)*SUMIF(Sheet1!A1:A3,Sheet2!B2:B10,Sheet1!C1:C3))

I used Sheet1, assuming you don't actually have a sheet named "sheet".

Is that something you can work with?

---

Ron Coderre

Microsoft MVP - Excel (2006 - 2010)

P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

Answer 2

Hi Ron,

Thanks.  That's the concept that I eventually went with.  Vlookup worked on a single line, and I was hoping that it would work for an array, but I guess not. 

Thanks again,

Eric

Answer 3

Had done that already.  Doesn't help.  Thanks, but try again.

Answer 4

To use an array formula, press CTRL+SHIFT+ENTER after entering the formula to get the desired result.

Refer the links given below for additional information on using array formula in Excel:

http://office.microsoft.com/en-us/excel/HP051983191033.aspx

http://office.microsoft.com/en-us/excel/HP052000201033.aspx

http://office.microsoft.com/en-us/excel/HP051999601033.aspx