• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with array formula

J

juwin_pt

New Member
Shouldn't value in column E12 be 5500? What am I doing wrong?


http://img714.imageshack.us/img714/934/capturejzd.jpg
 
No, I want the formula to pickup the value in column H, corresponding to value in column G. Hence the formula is vlookup(...,G:H,2,false), where G:H is my table, 2 is for the 2nd column.
 
File is attached at:

http://www.sendspace.com/file/sjt000


See this:

http://img707.imageshack.us/i/captureml.jpg/


It seems that the excel, is not doing Vlookup for each row. It is just doing the vlookup once. Is this a bug?
 
Hi again,


Also the use SUMIFS would be better if you are using 2007 as per Chandoo's lastest blog


http://chandoo.org/wp/2010/04/20/introduction-to-excel-sumifs-formula/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+PointyHairedDilbert+%28Pointy+Haired+Dilbert%29
 
No it doesn't. The formula gives me the desired values, but doesn't work, if I change something on the sheet.


Please see this sheet:

http://www.sendspace.com/file/dmvlso


See the array formula in the cell D20. The value in D20, is supposed to match with value in D18.


Note: I want to specifically use vlookup and array formula, and not autosum given by Excel.
 
Juwin

Try this in Cell H20


=SUMPRODUCT(1*(B:B&C:C=G:G),(D:D))


It Can't be in any column mentioned in the equation ie: B, C, G or D or you'll get a circular ref...
 
Hi,


With the formula you are using its just looking up the first row and returning 2, then when you sum that that is what you get.
 
You must log in or register to reply here.
Back
Top