There is a moment every spreadsheet user knows well: the formula works, and then it needs to work just a little harder. The original nested IF here is clean, logical, and functional. It checks column J against two values, confirms AC is under 49, and ensures AB does not read "CRMBOOK." When all conditions align, it returns a specific reference; otherwise, it falls back to J2. That is solid, defensible logic. But the request to add a condition on column I, one that changes the true reference based on what is found there, is where the real challenge begins. And it is a challenge worth meeting.
The instinct to solve this by adding another IF layer is understandable, but it is also the path to a formula that becomes brittle and difficult to read. The better move is to step back and think about what the data is actually asking for. Column I is not a secondary detail; it is a deciding factor. If the value in column I determines which AE reference should be returned, then the formula should treat that as a separate branch of logic, not an afterthought. The current structure uses AND to bundle conditions, but the new requirement introduces a fork in the road. That suggests a different approach: use IFS or a nested IF that isolates the column I check first, then applies the existing AND logic as the secondary condition. The true reference becomes a lookup based on I, not a static cell.
What this means practically is that the formula needs to separate the two layers of decision-making. First, ask what is in column I. If it matches one of the expected values, return the corresponding AE reference. If not, fall back to the original true branch, which is still governed by the AND conditions. The nesting does not have to be a nightmare. It just has to be structured so that each condition has a clear job. For example, IF(I2="Value1", $AE$5, IF(I2="Value2", $AE$6, original_true_value)). That keeps the logic readable and leaves room for future values without rewriting the entire formula.
The user is not asking for a revolution. They are asking for a formula that keeps up with their thinking. That is the real takeaway here. Nested formulas fail when they try to do everything at once. They succeed when they mirror the way a person actually reasons through the problem. The solution is not to force more conditions into the same AND block. It is to restructure the logic so that the new condition has its own lane. The original formula works because it is clear. The next version will work because it is clearer. That is the standard worth aiming for, and it is entirely within reach.