Prev: Date Diff  excluding working days
Next: Rounding
From: chilitodd on 13 Apr 2010 11:53 For my vlook up in excel 2007, I want to return data to populate cell B2 (xxxx) from column 3 ($21,745) of a data set when the value in cell A2 (360 19318) AND B1 (200801) match from the original data set. DATA SET 1 2 3 Dosfrom OP_Combined Claimed_Avg 200801 360 19318 $21,745 200803 360 19318 $22,462 200805 360 19318 $25,527 200806 360 19318 $22,462 A B c D 1 Row Labels 200801 200802 200803 2 360 19318 xxxx 3 360 47562 4 480 93620
From: Tom Hutchins on 13 Apr 2010 13:13 Here is one way. This example assumes your data set headings start in A1 on Sheet1, and your range with the lookup formulas starts in A1 on another sheet. This formula would go in B2 (adjust the ranges as needed): =SUMPRODUCT((Sheet1!$A$2:$A$15=B$1),(Sheet1!$B$2:$B$15=$A2),Sheet1!$C$2:$C$15) Copy this formula across & down as needed. You could get the same results by creating a pivot table with the OP_Combined as a row field, Dosfrom as a column field, and Claimed_Avg as the data field. Hope this helps, Hutch "chilitodd" wrote: > For my vlook up in excel 2007, I want to return data to populate cell B2 > (xxxx) from column 3 ($21,745) of a data set when the value in cell A2 (360 > 19318) AND B1 (200801) match from the original data set. > > DATA SET > 1 2 3 > Dosfrom OP_Combined Claimed_Avg > 200801 360 19318 $21,745 > 200803 360 19318 $22,462 > 200805 360 19318 $25,527 > 200806 360 19318 $22,462 > > > A B c D > 1 Row Labels 200801 200802 200803 > 2 360 19318 xxxx > 3 360 47562 > 4 480 93620 >
From: chilitodd on 14 Apr 2010 13:22 Yes, this worked perfectly! Thank you very, very, very much!!! Based on my data set, I naturally had to make some changes to the sheet names and cell references, but once that was fixed up  it worked like a charm! "Tom Hutchins" wrote: > Here is one way. This example assumes your data set headings start in A1 on > Sheet1, and your range with the lookup formulas starts in A1 on another > sheet. This formula would go in B2 (adjust the ranges as needed): > > =SUMPRODUCT((Sheet1!$A$2:$A$15=B$1),(Sheet1!$B$2:$B$15=$A2),Sheet1!$C$2:$C$15) > > Copy this formula across & down as needed. You could get the same results by > creating a pivot table with the OP_Combined as a row field, Dosfrom as a > column field, and Claimed_Avg as the data field. > > Hope this helps, > > Hutch > > "chilitodd" wrote: > > > For my vlook up in excel 2007, I want to return data to populate cell B2 > > (xxxx) from column 3 ($21,745) of a data set when the value in cell A2 (360 > > 19318) AND B1 (200801) match from the original data set. > > > > DATA SET > > 1 2 3 > > Dosfrom OP_Combined Claimed_Avg > > 200801 360 19318 $21,745 > > 200803 360 19318 $22,462 > > 200805 360 19318 $25,527 > > 200806 360 19318 $22,462 > > > > > > A B c D > > 1 Row Labels 200801 200802 200803 > > 2 360 19318 xxxx > > 3 360 47562 > > 4 480 93620 > >

Pages: 1 Prev: Date Diff  excluding working days Next: Rounding 