VLOOKUP? PIVOT TABLE? WHAT TO USE?
Posted by Michael on October 28, 2001 5:58 PM
I will be amazed -- and grateful -- if anyone knows the answer to this.
I have a worksheet with three columns of information: Date, TV Station Name, and Cost. There are about 400 unique station names, but not every station had an airing (and therefore a Cost entry) on every date. The data in the rows is currently sorted by date. Here's an example of the first few lines:
A B C D
Date Station Cost
1 1-Feb KBBB $100
2 1-Feb KCCC $200
3 2-Feb KAAA $150
4 2-Feb KBBB $300
5 2-Feb KDDD $250
6 3-Feb KCCC $ 50
In a second worksheet, I would like to arrange the foregoing data differently, like this:
A B C D E F G
1-Feb 2-Feb 3-Feb 4-Feb 5-Feb
1 KAAA 150
2 KBBB 100 300
3 KCCC 200 50
4 KDDD 250
What I'd like to say to Excel is: "For each cell in the 1-Feb column of Worksheet 2, take the station name in that row and see if it exists in the station column of Worksheet 1; if it does, AND if it exists in a row in Worksheet 1 where the date equals 1-Feb (the date atop the column in Worksheet 2), then return the cost value in that row (i.e., from Column D on Worksheet 1)." (I'd then like to copy that formula across the entire matrix I've made in Worksheet 2.)
Put another way, I'd like to say to Excel: "For each cell in Worksheet 2, please return the corresponding value from the Cost column in Worksheet 1 if, on Worksheet 1, there was an airing on the particular station listed on Worksheet 2 on the date atop the column in Worksheet 2. If not, leave the cell blank."
I have no idea how to get Excel to do this, so I would very much appreciate anyone's help. (I understand how VLOOKUP works, and am using it elsewhere in my workbook, but I have a feeling that it isn't enough to do this.)
(If it's easier to explain by telephone, I'd be happy to call you!)
[email @address removed]