Numbers formula not recognizing merged cells

OK... I *think* I see what you're saying, but it still isn't entirely clear from the screenshots... but, ultimately I think the problem is:

You cannot refer to sub-cells in a merged cell group.

That is, if you merge cells B20, B21, and B22 into a group, you can NOT reference cell B21 or B22. They don't exist, as far as the spreadsheet is concerned.

All references to a merged cell have to point to the upper-left cell in the range it covers.... so a merged cell B20:B22 can only be referenced via B20, and any other reference is doomed to failure.

To solve your problem, the easiest solution is probably to have a 'helper' column that fills in either the value in the Details column, or the previous value if that isn't available (e.g. is part of a merged cell).

To do this, I added a new column C and set the formula of cell C2 to:

=IFERROR(B2,C1)

What this does is try to grab the value from B2. If that fails (because B2 is subsumed by a merged cell) then it returns the value from the cell above.

Filling this formula down column C results in something that looks like:

So you can see that column C now has the 'most recent' value from Column B - it grabs column B's value when it can, and if it can't, it assumes the value above.

Once setup, you can hide column C since you don't want to see this, but the formulas will continue to work.

Now you can adjust your 'Dropped Off' and 'Picked Up' formulas to reference Column C rather than Column B, and avoid the issues with referencing merged cells.

There is an error in your formula that uses IF instead of IFS. It needs another IF in the middle of it. I don't know if that is part of your problem or not.

There is a problem in your IFS formulas unless B will only be "dropped off" or "dropped off and picked up" and will never be anything else. This may be why you are using IFERROR. It needs one more condition, the default fallback condition of TRUE for when the other conditions are FALSE.

=IFS(B20="dropped off","X",B20="Dropped off and picked up","Y", TRUE, "")

(I used "Y" for "dropped off and picked up" so I could tell them apart when testing it)

If you create the formulas first, then merge the cells second, it will all work out. First screenshot, each row refers to the cell in its own row. C20 refers to B20, C21 refers to B21, etc. Second screenshot I merged B20:B22 and now C20:C22 all refer to cell B20. Is this what you were trying to accomplish?

The issue with IFS() is valid, but not the root cause of this issue, I think.

IFS() requires any number of condition/result pairs. The first condition that passes is the one that returns its result.

If no condition passes, it throws an error.

So, conceptually, using IFERROR() to catch the case where no condition passes, is no different from adding a 'dummy' catch-all condition at the end. One might be slightly neater, but the outcome is the same.

In this particular case, though, the problem is not with the IFS(), it's that (in your example), the formula in C21 would be trying to reference B21, which is part of the merged cell B20:B22. Since a formula can't reference a cell that has been merged into another, the IFS() is doomed to failure.

There's no trivial way to get the formula in C21 to know if B21 is merged or not, which is why I suggested a helper column.

Maybe there's a terminology issue here, but I don't see any merged cells in your screenshots.

Your first formula in D21 seems to work - uses IF() to put an 'X' in the cell if the value is 'Dropped off & Picked Up'... fair enough.

The formula in C21 uses IFS(), but is invalid (which is why it's throwing the error).

IFS() takes any number of condition/result pairs, and the first condition that returns TRUE() is the one that gets its return calculation evaluated. If none of the conditions return a TRUE() value, then IFS() returns an error.

Reading between the lines, I'm guessing you want an "X" if the value in column B contains 'dropped off' (whether that's because the cell contains 'dropped off', or 'dropped off & picked up'.A far better way to do this is search for the text 'dropped off', rather than the entire text 'dropped off', or 'dropped off & picked up'... the latter would fail, for example, if the cell contained "dropped off and picked up'. This may be less of a problem if column B is calculated (maybe that's what you mean by 'merged cell'?), but I can't see that from here.

Either way, to have this formula return "X" if the string 'dropped off' exists in B21, use:

=IFERROR(IF(SEARCH("*dropped off*",B21),"X",""),"")

This first performs a wildcard search (using the * character around the string you're looking for). If that succeeds (the string is found) it returns an "X". If if fails, it returns an error, which is caught by the IFERROR() command to suppress the error flag.

You can use the same method for "*picked up*" in column D.

Now anytime column B contains the sub-string 'dropped off', column C will get an X', and any time column 'B' has 'picked up', column D will get an X, and they're complimentary.

Did I understand your goal correctly?

True about IFS and IFERROR. The formula worked as it was but I feel if there is a simple way to not create an error, I do that instead of using IFERROR. One problem with IFERROR is it will mask other errors like reference errors or other errors in the formula and you won't know why it isn't working as expected (if you notice at all) because there's no error triangle to tell you.