Thij_rahya wrote...
This is odd:

=IF(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))=0, J4,
T(INDIRECT("$M$"&((CELL("ROW")-3)
+(LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,FALSE))+2)/5))))
....

Your problem is caused by using CELL("ROW"). When you use the Formula
Evaluate tool, your active cell must be the cell containing the
formula, so CELL("ROW") would evaluate to the row number of the
formula's cell. However, when recalculating CELL("ROW") will evaluate
to the active cell's row number. For example, if this formula were in
X99 but G237 were the active cell, CELL("ROW") would evaluate to 237
rather than 99.

Try replacing the False term by

T(INDEX($M:$M,ROW()-3+LEN(VLOOKUP($A4,SAMPLE!$A$9:F201,6,0))+2)/5))

which uses ROW() rather than CELL("ROW"), where ROW() returns the
formula's cell's row number. This also eliminates the unnecessary
volatile INDIRECT call.

I'd also note that your lookup table has its top-left corner fixed but
its bottom-right corner floating. If you're using several of these
formulas copied/pasted or filled into different cells, leaving the
bottom-right corner floating is almost certainly a mistake.