Hello,
I'm trying to write an IF(AND formula that includes many aspects, but I'm getting lost in how many conditions there are. I need the report status to reflect different information based on 3 different columns:
For example, if Column A is 22 or 23, it needs to show "Not Allocated", 33 or 44 should show "Allocated", and 66 or 69 should show "Shipped."
But it also needs to pull in whether or not the shipment is late based on Column B. So, D2 should show "Late - Not Allocated" while D3 should show "Not Allocated."
In addition, if there is an appointment date scheduled, it should also pull through that it's been appointed. So, D4 should show "Late - Allocated - Appointed."
Please let me know if anyone can help. I've put together some formulas for pieces of it, but I can't seem to get it all to work together.
When the conditions become multi-dimensioned, it sometimes is easier to create a table of possible combinations and use INDEX and MATCH to navigate the table. You have given, I suspect, just a few examples of what might be a lot more possibilities in assessing data in columns A, B and C to determine what goes in to D.
But you've also left a few conditions to speculation (at least, it's not totally clear). For example, I'm assuming that what's involved in the statement below involves comparisons with today's date (i.e., in Excel, the function TODAY())
But it also needs to pull in whether or not the shipment is late based on Column B. So, D2 should show "Late - Not Allocated" while D3 should show "Not Allocated"
But you don't actually SAY that a comparison with TODAY() is the basis for that distinction.
Anyway, I'm attaching a sheet with a matrix for you to fill in (and extend as needed) to cover all the possibilities. Once that's done, we may be able to write a formula to find the appropriate intersection of these multiple conditions.
