View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default how do I lookup data based on two columns of data

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)


"bttreadwell" wrote in message
...
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.