Mike thanks for the reply, i only want a formula version if possible and i would further use that inside another formula, however i tried your suggestions but didn't produce the expect results, if i have data in lets say column E (excluding header) row 5 then the formula no matter where located should give the result $E$5, the formula you provide gave the result 4 when i had used 5 columns. I really would like to refrain from using a UDF.