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

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 A1:D1, 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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"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.