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

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.