Compare 2 columns, product type and date and depending on the date, return a number in a 3rd column

SMB

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")))))))))))))))))

Nic Larsen

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.

Matthew J McAteer

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

SMB

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?

Nic Larsen

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.