Advanced formula: IF X = A, B, OR C, AND Y = D, REPORT BACK TEXT

[brhelpneeded]

Would love some advice on this! I'm honestly not sure if it's possible. It feels like it *should* be.

In one sheet, I have a list of products, each with varying release months. Adjacent to the launch month, I have plotted the succeeding two months to create a 3 month total "New Release Period" i.e.

Column A / Column B / Column C / Column D / Column E
Product / Month 1 (aka Release Month) / Month 2 / Month 3 / Value to Return
Product 1 / Apr-12 / May-12 / Jun-12 / New Release
Product 2 / Jun-14 / Jul-14 / Aug-14 / New Release
Product 3 / Apr-15 / May-15 / Jun-15 / New Release
etc.

In a second sheet, I have thousands of rows of monthly performance data, plotting activity per product over a number of years by month. This sheet includes, among other things, the Product Name (which is unique per product) and the corresponding month of performance data. i.e.

Column A / Column B / Column C
Month / Product / Performance
Apr-09 / Product 1 / 6584
Apr-09 / Product 2 / 2342
May-09 / Product 1 / 7354
May-09 / Product 2 / 1243
etc.

I want to create a column in the second sheet that specifies the "New Release Period" (i.e. first 3 months of activity per product) vs. each products "Catalogue Period".

I'm envisioning some form of INDEX/MATCH plus IF/THEN function. So, the argument being:

IF cell in column A of sheet 2 = value in sheet 1 of column B, C, OR D, AND IF column B in sheet 2 = value in sheet 1 of column A, return value "New Release"

Anyone have any ideas on a formula that could deal with such a complex argument? Or am I just over-reaching?

All ideas really welcome!

 

[petertenthije]

Would it not be a lot easier to VLOOKUP only the last month that a product is still considered a new release? Then you can consider any sale made in the last new release month or earlier as "New release"? Or is it possible that a product is counted as a "catalogue product" prior to the "new release" period? For instance because product numbers are recycled or modified.


=IFERROR(IF(VLOOKUP($B2;Sheet3!$A:$D;4;FALSE)>=Sheet4!$A2;"Release period";"Catalogue period");"Catalogue period")

 

[henrik2h]

Clear as mud, "activity per product"??, "catalogue period"?? "new release period"?? none of these are mentioned in the actual data.

I believe this might do what you are after, you must change the references obviously

sumproduct((Sheet1!$A$2:$A$100=sheet2!B2)*((Sheet1!$B$2:$B$100=sheet2!A2)+(Sheet1!$C$2:$C$100=sheet2!A2)+(Sheet1!$D$2:$D$100=sheet2!A2))*(Sheet1!$E$2:$E$100))

copy formula down

explanation:
sum values if
1. product is the same
2. Sheet2 Date is equal to Month 1 OR Date is equal to Month 2 OR Date is equal to Month 3
3. sum these values

 

[brhelpneeded]

Thanks so much to both of you for your thoughts!

@henrik2h: Sorry for being clear as mud! I know, my explanation was rough and difficult to follow. Hoping the following might help explain my logic better than my rubbish words:

I've made an example sheet with the two tabs I tried (and failed!) to explain properly, and provided a link to download it at the base of this message. I've highlighted column D in tab 2 "EXAMPLE SHEET 2" as the column I'm ideally trying to populate.

You're right; I didn't include a value to explain 'catalogue period'. Thought I could just populate that after the fact. i.e. cells that do not return a "New Release" value must therefore be "Catalogue".

@peterenthije: thank you for the quick reply! You're right in that the assumption is no month *before* the last new release month should be ever classed as catalogue. Product names are all unique, so perfect for a vlookup. Had a try with your suggested formula, though think I'm bungling the logic. No pressure at all to download the below and take a look, though hoping it might help clarify the right formula logic!

thanks again, both!

https://www.wetransfer.com/download...06a2e7e85842f97dd1e4f34a20151105230417/89dea4

 

[brhelpneeded]

Clear as mud, "activity per product"??, "catalogue period"?? "new release period"?? none of these are mentioned in the actual data.

I believe this might do what you are after, you must change the references obviously

sumproduct((Sheet1!$A$2:$A$100=sheet2!B2)*((Sheet1!$B$2:$B$100=sheet2!A2)+(Sheet1!$C$2:$C$100=sheet2!A2)+(Sheet1!$D$2:$D$100=sheet2!A2))*(Sheet1!$E$2:$E$100))

copy formula down

explanation:
sum values if
1. product is the same
2. Sheet2 Date is equal to Month 1 OR Date is equal to Month 2 OR Date is equal to Month 3
3. sum these values

ah, ok. This began to report back data once I changed the value in sheet 1 column E to a number rather than text (I replaced "New Release" with a value "1"). However I think I have made a mis-step along the way as only the first few months of data are coming through as "1" and all other data is coming through as #VALUE!.

I replied to the overall thread with a link to download an example data sheet. Better to use example data than try to continue with my poor explanation! Thank you again for your response.

 

[petertenthije]

The earlier formula seems to work for me after changing the names of the worksheet references ('EXAMPLE SHEET 1' instaed of SHEET3 etc).

The below formula should work based on the example file you provided.

=IFERROR(IF(VLOOKUP($B2;'EXAMPLE SHEET 1'!$A:$D;4;FALSE)>=$A2;"Release period";"Catalogue period");"Catalogue period")

What (language) version of Excel are you using? Depending on the version the formula's parameters are seperated either by semicolon or by a comma. In my version they are semicolon so maybe you have to replace them with commas for the formula to work in your version of Excel?

 

[brhelpneeded]

This is a totally sound idea and much more logical than my attempts so far, thank you! However, I'm struggling to be able to successfully match the last new release month to the specific product (as it is a variable per product). My attempts so far keep defaulting to just the top example month range rather than successfully pinpointing the month specific to the product. I am no doubt being unbelievably slow and almost definitely doing something wrong that should be simple. I've uploaded an example data sheet here in the hope it will do a better job of explaining the data ranges & variables than I have thus far

https://www.wetransfer.com/download...06a2e7e85842f97dd1e4f34a20151105230417/89dea4

 

[brhelpneeded]

The earlier formula seems to work for me after changing the names of the worksheet references ('EXAMPLE SHEET 1' instaed of SHEET3 etc).

The below formula should work based on the example file you provided.

=IFERROR(IF(VLOOKUP($B2;'EXAMPLE SHEET 1'!$A:$D;4;FALSE)>=$A2;"Release period";"Catalogue period");"Catalogue period")

What (language) version of Excel are you using? Depending on the version the formula's parameters are seperated either by semicolon or by a comma. In my version they are semicolon so maybe you have to replace them with commas for the formula to work in your version of Excel?

Yep, I was being daft and copying your semi-colons. Once converted to the commas it worked like a charm! Thank you so so much! You've saved the day!

 

[petertenthije]

You're welcome!