Why Formula in same cell giving different result than putting same formula in different cells

[dellzy]

Hi Excel Expert,

I want to have one formula in one cell only, not breaking them into different cells. I usually able to do this no matter how long the formulas are and the result will be just the same as if I breakdown into smaller formulas in separate cells. However, I still don't understand why it is not happening for the below formula :-

=IF(ISNUMBER(SEARCH("has been changed to",INDIRECT("F"&ROW(INDEX($F$2:$F$87,MATCH(1,(B85=$B$2:$B$87)*(Q85=$C$2:$C$87),0)))+1)))=TRUE,"Y","N")

am putting the above formula in cell U84, and result shows "N"

but when i breakdown into 2 different cells :-
U84 = INDIRECT("F"&ROW(INDEX($F$2:$F$87,MATCH(1,(B85=$B$2:$B$87)*(Q85=$C$2:$C$87),0)))+1)
U85 = IF(ISNUMBER(SEARCH("has been changed to",U84))=TRUE,"Y","N")

the output in U85 will be "Y".

I didn't change anything in the formula except breaking them into 2 different cells and reference the first cell in 2nd cell.

fyi, the result for INDIRECT("F"&ROW(INDEX($F$2:$F$87,MATCH(1,(B85=$B$2:$B$87)*(Q85=$C$2:$C$87),0)))+1) is "Assignment has been changed to Completed."

Is there anything I missed out here? I really could use some help to correct my formula to ensure this formula ultimately can be put in one cell only.

Appreciate your expertise.

Tqvm in advance.
DZ

 

[Eric W]

That whole INDIRECT construct seems suspicious to me, and is hard to read. (And is volatile.) And when I run the Evaluate Formula tool, that's where it dies. Instead, I'd recommend something like this:

=IF(ISNUMBER(SEARCH("has been changed to",INDEX($F$2:$F$87,MATCH(B85&"|"&Q85,$B$2:$B$87&"|"&$C$2:$C$87,0)))),"Y","N")

with Control+Shift+Enter.

I was able to adapt your original formula to work, but I'd still stick with this one.

 

[dellzy]

Hi Eric W,

Thank you for the swift response. Your formula does work to populate the same result "Y" if I only run it on one cell. This formula actually a partial formula that I have created and this formula supposed to be computed when the conditions in main formulas are not met. Somehow the reason why i put ROW(INDEX($F$2:$F$87,MATCH(1,(B85=$B$2:$B$87)*(Q85=$C$2:$C$87),0)))+1) is due to the fact that there is no fix pattern in the data. I would appreciate if you can also provide the original formula that you mentioned as workable already. I cannot use your revised formula because it has affected the result in my main formula.

Just fyi, the whole formula is supposed to be like below:-

=IF(R85="","",IF(B$2=B85,INDEX($F$2:$F$87,MATCH(1,(B85=$B$2:$B$87)*(Q85=$C$2:$C$87),0)),IF(ISNUMBER(SEARCH("has been changed to",INDIRECT("F"&ROW(INDEX($F$2:$F$87,MATCH(1,(B85=$B$2:$B$87)*(Q85=$C$2:$C$87),0)))+1)))=TRUE,INDIRECT("F"&ROW(INDEX($F$2:$F$87,MATCH(1,(B85=$B$2:$B$87)*(Q85=$C$2:$C$87),0)))+2),INDIRECT("F"&ROW(INDEX($F$2:$F$87,MATCH(1,(B85=$B$2:$B$87)*(Q85=$C$2:$C$87),0)))+1))))

with Ctrl+Shift+Enter

I managed to get other formulas to come out with the output that I want except for the one I mentioned earlier.

Your generous help is much appreciated.

Tqvm in advance.

 

[Eric W]

I don't really understand what you mean when you say it works if you run it on one cell. Do you mean that when you copy it to other cells it doesn't return the results you want? That could mean that you have an issue with the relative/absolute references.

Next, I still really don't like the INDIRECT construct, but to get it to work, I had to put MAX around your ROW functions, e.g.

"F"&ROW(...) becomes "F"&MAX(ROW(...))

This converted an array with one numeric element {55} to a number 55, which could then be properly handled by INDIRECT. You can see the difference if you use the Evaluate Formula tool.

As far as I can tell without extensively deconstructing and testing your formula, the main difference with the first INDEX($F$2:$F$87,MATCH(1 ... section and the sections using INDIRECT is that the latter sections are offset by 1 or 2 rows. You can easily handle that without INDIRECT, something like:

INDEX($F$3:$F$88,MATCH(1,(B85=$B$2:$B$87)*(Q85=$C$2:$C$87),0))

See how the lookup array is offset from the matching arrays by 1?


Finally, just as an FYI, it's usually easier to get help by just showing a sample of your data and some expected results. Showing a large formula and saying "it doesn't work" doesn't offer a lot to work with. Most people won't bother to deconstruct something that big.

Hope there's something useful for you here!