J juwin_pt New Member Apr 19, 2010 #1 Shouldn't value in column E12 be 5500? What am I doing wrong? http://img714.imageshack.us/img714/934/capturejzd.jpg
Shouldn't value in column E12 be 5500? What am I doing wrong? http://img714.imageshack.us/img714/934/capturejzd.jpg
J juwin_pt New Member Apr 19, 2010 #3 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.
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.
oldchippy Active Member Apr 19, 2010 #4 Hi, I didn't check that, but can you upload a file rather than a picture? http://rapidshare.com/
J juwin_pt New Member Apr 19, 2010 #5 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?
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?
oldchippy Active Member Apr 19, 2010 #6 Hi, Does this work for you? =SUMPRODUCT(--(A:A&B:B=E:E),--(C:C),--(F:F))
oldchippy Active Member Apr 20, 2010 #7 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
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
J juwin_pt New Member Apr 20, 2010 #8 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.
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.
Hui Excel Ninja Staff member Apr 20, 2010 #9 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...
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...
oldchippy Active Member Apr 20, 2010 #10 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.
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.
oldchippy Active Member Apr 21, 2010 #12 No, I would say it's just not the right formula for the job you want.