IF {Status} is "X" or "XX" and {Date} is in the coming 90 days

Protonsponge

Hello,

In the formula below I am asking if the status in a reference sheet is "Pending Submission" or "Pending Approval" then display "No" in a symbol column.

I would be very grateful for any best practice on how to structure the formula to add in if {status} is "Pending Submission" or "Pending Approval" and a {Date Column} is in the next 90 days.

=IF(OR(INDEX(COLLECT({Status}, {country code}, [Country Code]@row), 1) = "Pending Submission", INDEX(COLLECT({Status}, {country code}, [Country Code]@row), 1) = "Pending Approval"), "No", "")

Very thankful in advance,

Protonsponge

Paul Newcome

The Range/Criteria set should go inside of each of the COLLECT functions.

Protonsponge

Thank you very much @Paul Newcome, I got going again with your help - very grateful.

=IF(OR(INDEX(COLLECT({Status}, {country code}, [Country Code]@row, {Date column}, <=TODAY(+90)), 1) = "Pending Submission", INDEX(COLLECT({Status}, {country code}, [Country Code]@row, {Date column}, <=TODAY(+90)), 1) = "Pending Approval"), "No", "")

Paul Newcome

You would add another range/criteria set to the COLLECT function(s) that need to evaluate for the date.

Protonsponge

Thank you @Paul Newcome for picking up the Q. I was trying to get something like below to work but I am getting #INVALID DATA TYPE.

Is this going down the wrong path?

=IF(AND(OR(INDEX(COLLECT({Status}, {country code}, [Country Code]@row), 1) = "Pending Submission", INDEX(COLLECT({Status}, {country code}, [Country Code]@row), 1) = "Pending Approval"), {Date column}, <=TODAY(+ 90)), "No", "")

Protonsponge

Paul Newcome

The Range/Criteria set should go inside of each of the COLLECT functions.

Protonsponge

Thank you very much @Paul Newcome, I got going again with your help - very grateful.

=IF(OR(INDEX(COLLECT({Status}, {country code}, [Country Code]@row, {Date column}, <=TODAY(+90)), 1) = "Pending Submission", INDEX(COLLECT({Status}, {country code}, [Country Code]@row, {Date column}, <=TODAY(+90)), 1) = "Pending Approval"), "No", "")

Paul Newcome

Happy to help. 👍️