Looking back through cells in a column to find Specific Text

Please refer to the below thread marked as solved, however I have encountered another problem with the same item:

Excel Help Forum » Microsoft Office Application Help - Excel Help forum » Excel Programming » [SOLVED] Looking back through cells in a column to find Specific Text(s)

Please see attached example.

Column F is required to check column A for current row, as well as look back up the rows until it encounters an Origin. If the origin is Either NADI or PHUKET, Column F must be 0.5, if not it must be 1.0.

At present, If i delete any formulas in column A (as shown in rows 14-19) after one of the above origins, it works fine. As soon as I copy the formula down (eg rows 27-42), it then reverts column F back to 1 for all rows except where the origin actually appears.

How can I amend my formula please in column A or F so I dont have to go through manually and delete formulas between origins that are showing in column A.

Also, could you please check my use of the formula in column F where it refers to PHUK and NADI to ensure i have used it correctly?

For nulls, you should use 2 consecutive double quotes, not with a space between...

I also changed your formula around to use less functions and be more readable...

=IF(AND($G7<>"To",$G7<>"From",ISTEXT($G7),$G7<>""),G7,"")

copied down.