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