• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help on returning values

F

Flashart80

New Member
Hi All


Can someone help me here?


I have in my sheet a data list with the following columns:


account|campaign|impressions|clicks|cost|avg_position


I then have all the accounts and their relevant campaigns. I am trying to construct a formula that looks at an account value in cell A1, then looks to the above list and returns all the relevant campaigns and their impressions, clicks etc.


Thanks!
 
Flashart

Have you had a look at using Index(Match())

It will be something like

=Index(Lookup area, Match($A$1,Account Column,0), Column No)


Best if you can post your data somewhere:

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Hui


Yes I have had a look at index and match but could not get the 2nd result. It returns the first result which is great, but now I need to get as many campaigns that belong to that account. Could you tell me how to get the 2nd,3rd etc campaigns?


Many thanks

Peter
 
What do you want to do when for example Adobe has 2 Customer Relations lines ?
 
What about putting a list of the Unique Campaigns down the side and then when selecting a Company all those are updated together?


eg:


Adobe

[pre]
Code: 
impressions	clicks	cost
Accounting		5289	619	63
Advertising		0	0	0
Asset Management	2012	766	3
Customer Relations	16883	1389	203
Customer Service	0	0	0
Finances		0	0	0
Human Resources		0	0	0
Legal Department	8647	897	188
Media Relations		0	0	0
Media Relations		0	0	0
Payroll			0	0	0
Public Relations	24529	1158	198
Quality Assurance	6184	395	50
Research and Development	13816	585	166
Sales and Marketing	0	0	0
Tech Support		0	0	0
[/pre]
Which of course can be done in a Pivot Table or using Formula
 
Hi


Yes that sounds great. I was thinking about using data validation to have a drop down to select the company. Whilst I can do that bit, I wasn't sure on how to update the campaigns, cost etc.
 
Put in G12:G26

[pre]
Code: 
Accounting
Advertising
Asset Management
Customer Relations
Customer Service
Finances
Human Resources
Legal Department
Media Relations
Payroll
Public Relations
Quality Assurance
Research and Development
Sales and Marketing
Tech Support[/pre]
H11:J11

impressions	clicks	cost


G10: Adobe


H12:

=SUMPRODUCT(($A$2:$A$101=$G$10)*($B$2:$B$101=$G12),C$2:C$101)

Copy across and down


Add a Drop down to G10 if required


Enjoy
 
Ah I see. The issue is that the campaigns change every week. This would then mean a manual update of the campaign list which I am trying to avoid. Is there a way of essentially saying, "look at cell K1 (where I enter an account name), for that account go and retrieve the campaigns that correspond with that account". Almost like a vlookup that I can wrap in an if statement to show blank cells if there is nothing and drag down as many as I need?
 
You must log in or register to reply here.
Back
Top