Index Array with Double If Argument

[mco5044]

Hi,

We are trying to build a formula to analyze Amazon Ad Campaign Data.

We have 2 sheets. The first is a raw data sheet where we paste the info in. The 2nd is a bid adjuster sheet which calculates based on the raw data info

Our Auto Campaigns are working and look like this:

Raw Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Campaign Name[/TD]
[TD]Ad Group Name[/TD]
[TD]Ad Sku[/TD]
[TD]Keyword[/TD]
[/TR]
[TR]
[TD]Campaign 1[/TD]
[TD]Ad Group 1[/TD]
[TD]abcd[/TD]
[TD]KW1[/TD]
[/TR]
[TR]
[TD]Campaign 1[/TD]
[TD]Ad Group 1[/TD]
[TD]bcde[/TD]
[TD]KW2[/TD]
[/TR]
</tbody>[/TABLE]

Adjuster Sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Campaign[/TD]
[TD]Product Name Campaign[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Keyword[/TD]
[TD]Impressions[/TD]
[/TR]
[TR]
[TD]KW1[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]

In Cell B1 we use data validation to pick the campaign name from a drop down.

This formula works and scrolls through the data and picks out the kw for the campaign we are looking for. It's an array formula so it drags down and finds only the kws for that specific campaign

=INDEX('Auto Raw Data'!$A$1:$S$4500,SMALL(IF('Auto Raw Data'!$A$1:$A$4500='Auto Bid Adjuster'!$B$1,ROW('Auto Raw Data'!$A$1:$S$4500)),ROW(1:1)),4)

The trouble we are having is with our manual campaigns because we have to check for the proper campaign and the proper ad group within that campaign. The 2nd if statement is tripping up the formula and right now we are getting a #NUM error. I think the issue is happening in the Small function

Manual Data Sheet Example

[TABLE="width: 500"]
<tbody>[TR]
[TD]id[/TD]
[TD]type[/TD]
[TD]campaign name[/TD]
[TD]budget[/TD]
[TD]start[/TD]
[TD]end[/TD]
[TD]target type[/TD]
[TD]ad group name[/TD]
[TD]max bid[/TD]
[TD]keyword[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]dasda[/TD]
[TD]Product Campaign[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Product Ad Group[/TD]
[TD]0[/TD]
[TD]Keyword[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]sdad[/TD]
[TD]Product Campaign[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Product Ad Group[/TD]
[TD]0[/TD]
[TD] Keyword[/TD]
[/TR]
</tbody>[/TABLE]

Our bid adjuster sheet looks exactly the same as the auto campaign I showed above. The only difference is a few extra random columns we dont need the info for

Here is our formula with the extra if statement to check that we have the Campaign and proper Ad Group

=INDEX('Manual Raw Data'!$A$1:$U$4500,SMALL(IF('Manual Raw Data'!$C$1:$C$4500='Manual Bid Adjuster'!$B$1,IF('Manual Raw Data'!$H$1:$H$4500='Manual Bid Adjuster'!$B$2,ROW('Manual Raw Data'!$A1:$U$4500))),ROW(1:1)),10)

Any help is massively appreciated. We are sooo close!

Thank you very much

- Mike

 

[ExcelGuyDude]

These explanations of how the sheets are set up is not very clear. So based on what you are wanting to get, you might want to use a different formula... SUMPRODUCT. This works very well with multiple criteria, and you don't even need to use the "Ctrl + Shift + Enter" method that an Index Array uses.

I'm having a hard time trying to imagine what you're doing here, but I'm guessing you want something like this:

=SUMPRODUCT(--('Manual Raw Data'!$C$1:$C$4500='Manual Bid Adjuster'!$B$1),--('Manual Raw Data'!$H$1:$H$4500='Manual Bid Adjuster'!$B$2),('Manual Raw Data'!$J$1:$J$4500))

A few websites that go into more depth on how to use the SUMPRODUCT function:
Excel SUMPRODUCT formula - Syntax, Usage, Examples and Tutorial | Chandoo.org - Learn Microsoft Excel Online
Array formulas and sumproduct - Desktop Liberation
https://social.msdn.microsoft.com/F...-to-using-sumproduct-in-an-array?forum=isvvba

 

[mco5044]

Unfortunately the SumProduct returns numbers not the text from the cell. That's why we have been using Index.

Sorry if I wasn't totally clear on the purpose here.

In the raw data tab there can be say 30 campaigns and each campaign may have 2 different ad groups associated with it.

The Adjuster tab basically sorts through the raw data tab and only pulls out the info we want based on the filters we set up top in those B cells (Campaign and Ad Group).

This way each week we can pull a data file from Amazon and paste it into the Raw Data. Then over in the Adjuster tab we automatically have a nicely formatted report with additional formulas and stuff we have added so we can make logical decisions based on the data.

The purpose of the formula in question is to copy over the exact keywords from the raw data sheet that match up to the campaign and ad group we set up top. After we run our analysis and adjust bids on that particular campaign, we just change the filter up top to another campaign and the new set of keywords automatically get dropped in.

The formula we are working on is the exact same as the working formula I posted above in the description. The only thing that has changed is we are adding a second criteria

Does that help?

Thanks again for looking into this

 

[minhhieule89]

Try

=INDEX('Manual Raw Data'!$A$1:$U$4500,SMALL(IF(('Manual Raw Data'!$C$1:$C$4500='Manual Bid Adjuster'!$B$1)*('Manual Raw Data'!$H$1:$H$4500='Manual Bid Adjuster'!$B$2),ROW('Manual Raw Data'!$A1:$U$4500))),ROW(1:1)),10)

cheer

 

[mco5044]

I actually just found a work around. It doesn't solve the core issue of 2 separate criteria, but I changed all of the Ad Group Names to make them completely unique to each product line. That way we can test only against the 1 criteria of Ad Group instead of Ad Group and Camapaign

 

[ExcelGuyDude]

Unfortunately the SumProduct returns numbers not the text from the cell. That's why we have been using Index.

I've actually used SumProduct on many instances to grab text values from cells, just like how an Index formula grabs it. Take a look at those websites, and Google a bit about SumProduct. It's one of the most under-rated formulas that people are generally afraid to use it because it can get confusing.

 

[mco5044]

I've actually used SumProduct on many instances to grab text values from cells, just like how an Index formula grabs it. Take a look at those websites, and Google a bit about SumProduct. It's one of the most under-rated formulas that people are generally afraid to use it because it can get confusing.

I definitely will! Thank you very much for the advice. I'm definitely guilty of being afraid of that formula lol