VLOOKUP help

[john222]

Hey,

I have 2 spreadsheet files, one contains campaigns and keywords columns, the other contains campaigns, keywords, orders and revenue columns.

I need to extract the orders and revenue data from the second sheet for each keyword matched up with the data on the first sheet.

However, some identical keywords are included in more than one campaign so I need to make sure that the keyword sales data is only matched up if it is in the same campaign.

How can you use a VLOOKUP to say "if the keyword in the first sheet is in campaign X, and the keyword in the second sheet is the same, and also in campaign X, then return the order value column, otherwise return 0."

Any help would be much appreciated,

John

[davesexcel]

A sample would be beneficial in getting an answer.

[john222]

A sample would be beneficial, in getting an answer.

Hi thanks,

Here's a sample of the data (the actual sheets contain thousands of entries each)

[Blake 7]

John - can you post your speadsheet. You would get an answer alot quicker and make it easier for potential helpers.

cheers

[john222]

John - can you post your speadsheet. You would get an answer alot quicker and make it easier for potential helpers.

cheers

Thanks very much,

John

[martindwilson]

not sure how this is supposed to work column a of 1b doesnt contain all the words in column a of 1a
its perfectly possible to match on 2 criteria against two columns if you want

[john222]

not sure how this is supposed to work column a of 1b doesnt contain all the words in column a of 1a
its perfectly possible to match on 2 criteria against two columns if you want

Is there a way to have a formula to match on 2 criteria against 2 columns and only return the sales volume if both of the criteria is met (i.e. if the keyword AND campaign are the same) - that would be perfect.

Cheers

[Blake 7]

Hi John - not sure if this will do what you want....... see attachment

=SUMIF(A!$B$2:$B$26,B!C2,A!$C$2:$C$26)

[john222]

Hi John - not sure if this will do what you want....... see attachment

=SUMIF(A!$B$2:$B$26,B!C2,A!$C$2:$C$26)

Thanks for that - it looks nearly there. However, I am having a problem with the highlighted in yellow fields. For instance the petite dresses keyword shouldn't pull through any sales data as it is in a different campaign to the petite dresses keyword in the other sheet.

Cheers

John

[Blake 7]

Ahhhh - sorry. I missed you post which said.......

Is there a way to have a formula to match on 2 criteria against 2 columns and only return the sales volume if both of the criteria is met (i.e. if the keyword AND campaign are the same) - that would be perfect.

My soultion does not match against multiple criteria, ie Campaign and Keyword. This is possible to do but I think we may need a SUMPRODUCT function or SUMIF with an IF. I'm no expert but will try to help. In the mean time google SUMIF with multiple criteria. Don't worry you will get there. This site is great for help.

Someone better than me will jump in when we get stuck!!

Let me have a quick look into this..........

[martindwilson]

sumproduct will prob do it but i couldn't see what the criteria were!