Compare 2 columns, product type and date and depending on the date, return a number in a 3rd column
I have a working formula that looks at a product type column and depending on the type, inserts a number in another column. The issue I am having is I need to now include comparing the ship date with the product type to return a number. If the ship date is between 06/01/2024 and 08/31/2024 and the product type is A, the number should return 34; if the ship date is not between those dates, the number should return 28. I am unsure what formula to use to look at the 3 columns and return the number. Also this would only be the case for 2 product lines; all the other product lines would not need to look at the ship date so how would I combine 2 formulas to return a number?
Current working formula just to return a number: =IF([PRODUCT TYPE]@row = "A", "30", IF([PRODUCT TYPE]@row = "B", "28", IF([PRODUCT TYPE]@row = "C", "24", IF([PRODUCT TYPE]@row = "D", "30", IF([PRODUCT TYPE]@row = "E", "28", IF([PRODUCT TYPE]@row = "MAXAM1", "28", IF([PRODUCT TYPE]@row = "METRO", "30", IF([PRODUCT TYPE]@row = "F", "32", IF([PRODUCT TYPE]@row = "G", "30", IF([PRODUCT TYPE]@row = "H", "30", IF([PRODUCT TYPE]@row = "I", "30", IF([PRODUCT TYPE]@row = "J", "30", IF([PRODUCT TYPE]@row = "QUATTRO PLYFORM", "30", IF([PRODUCT TYPE]@row = "K", "30", IF([PRODUCT TYPE]@row = "L", "30", IF([PRODUCT TYPE]@row = "M", "30", IF([PRODUCT TYPE]@row = "N", "30")))))))))))))))))
Answers

Try this:
=IF(AND([SHIP DATE]@row >= DATE(2024, 6, 1), [SHIP DATE]@row <= DATE(2024, 8, 31), [PRODUCT TYPE]@row = "A"), 34, IF(AND([PRODUCT TYPE]@row = "A", OR([SHIP DATE]@row > DATE(2024, 8, 31), [SHIP DATE]@row < DATE(2024, 6, 1))), 28, IF([PRODUCT TYPE]@row = "C", 24, IF([PRODUCT TYPE]@row = "F", 32, IF(OR([PRODUCT TYPE]@row = "B", [PRODUCT TYPE]@row = "E", [PRODUCT TYPE]@row = "MAXAM1"), 28, IF(OR([PRODUCT TYPE]@row = "A", [PRODUCT TYPE]@row = "D", [PRODUCT TYPE]@row = "METRO", [PRODUCT TYPE]@row = "G", [PRODUCT TYPE]@row = "H", [PRODUCT TYPE]@row = "I", [PRODUCT TYPE]@row = "J", [PRODUCT TYPE]@row = "QUATTRO PLYFORM", [PRODUCT TYPE]@row = "K", [PRODUCT TYPE]@row = "L", [PRODUCT TYPE]@row = "M", [PRODUCT TYPE]@row = "N"), 30, 28))))))
I'm sure there's a way to streamline this formula, but this seems to work.

Hi @SMB,
Try this:
=IF(AND([Product Type]@row = "A", [Ship Date]@row >= DATE(2024, 6, 1), [Ship Date]@row <= (DATE(2024, 8, 31))), "34", IF([Product Type]@row = "A", "28", IF([Product Type]@row = "B", "28", IF([Product Type]@row = "C", "24", IF([Product Type]@row = "D", "30", IF([Product Type]@row = "E", "28", IF([Product Type]@row = "MAXAM1", "28", IF([Product Type]@row = "METRO", "30", IF([Product Type]@row = "F", "32", IF([Product Type]@row = "G", "30", IF([Product Type]@row = "H", "30", IF([Product Type]@row = "I", "30", IF([Product Type]@row = "J", "30", IF([Product Type]@row = "QUATTRO PLYFORM", "30", IF([Product Type]@row = "K", "30", IF([Product Type]@row = "L", "30", IF([Product Type]@row = "M", "30", IF([Product Type]@row = "N", "30"))))))))))))))))))
You could also try listing all of your Product Types and their Return Numbers on a separate sheet, then use the cross sheet formula function to bring back the values using an INDEX/COLLECT formula, but this should work.
Hope this helps 😊
Matthew

Thanks for all the information but I need 3 products to look at the date and all the rest of the products just return a number. These formulas work but only for 1 product that looks at the dates. I want to list the 3 products first that need to compare it to the date followed by the rest that will return a number.
I don't want to use an INDEX/COLLECT formula but will if I have to. I was thinking that there has to be an OR somewhere in the formula from Matthew such as:
=IF(AND([Product Type]@row = "A", [Ship Date]@row >= DATE(2024, 6, 1), [Ship Date]@row <= (DATE(2024, 8, 31))), "34", IF([Product Type]@row = "A", OR =IF(AND([Product Type]@row = "B", [Ship Date]@row >= DATE(2024, 6, 1), [Ship Date]@row <= (DATE(2024, 8, 31))), "34", IF([Product Type]@row = "B", ETC....
What do you think?

I don't think it's clear what all of the conditions are you are trying to capture in your descriptions above. Index/Match is one way to go about solving or list out all of the scenarios (if/then/this or then this), it would be easier to piece together the formula you are looking for.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!