how do I lookup data based on two columns of data

[Guest]

I have data in 4 columns, I want to look at two (or three if it works)
columns and if data matches, put 4th column data in that cell.

Ex.

Here is my source data.

A B C D
1 401 1 50204 $200
2 401 1 50206 $200
3 401 2 50204 $200
4 401 2 50206 $200
5 401 3 50204 $200
6 401 3 50206 $200
7 401 4 50204 $200
8 405 1 50204 $200
9 405 1 50206 $200
10 405 2 50204 $200
11 405 2 50206 $200
12 405 3 50204 $200
13 405 3 50206 $200
14 405 4 50204 $200
etc.

Column B refers to a month of the year.

I want to turn it into this type of format:

For 401

A B C D E
1 1 2 3 4
2 50206 $200 $200 $200 $200
3 50206 $200 $200 $200 $200

Row 1 refers to the same months of the year.

And then do the same thing for 402.

Is this possible??? I tried to use a lookup function, but I could not
figure out how to look for 2 variables and then if they both match the result
be a third variable. It would be a simple cut and paste, but my source data
is 50K+ lines long it covers 50 units (column A), 2500 account codes (column
C), and the values for those respective code (column D).

Any help would be greatly appreciated.

 

[Bob Phillips]

For 401

=SUMPRODUCT(--(Sheet1!$A$1:$A$200=401),--(Sheet1!$B$1:$B$200=B$1),--(Sheet1!
$C$1:$C$200=$A2),Sheet1!$D$1:$D$200)

and so on.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Max]

Another option .. perhaps quite ideal to use a pivot table (PT)
(Takes only a few clicks & drag-n-drops to set-up)

Here's a sample construct:
http://cjoint.com/?lte0RUonJK
Pivot_Table_bttreadwell_wks.xls

Assume the source data is in cols A to D,
with labels in A11, data from row2 down, viz.:

Key Mth Acc Val
401 1 50204 $200
401 1 50206 $200
401 2 50204 $200
etc

Select any cell within the table

Click Data > Pivot Table Report

Click Next > Next

In step3 of the wiz.:

Drag and drop "Key" within the ROW area
Drag and drop "Acc" within the ROW area, below "Key"

Double-click on "Key", select "None" for subtotals > OK
Double-click on "Acc", select "None" for subtotals > OK

Drag and drop "Mth" within the COLUMN area
Drag and drop "Val" within the DATA area
(It'll appear as "Sum of Val")

Click Finish

The PT will be created in a new sheet just to the left,
giving the summary you want