VALUE OFFSET function not working as expected

Hello All,

I am using below function to convert my value to number and I am using it in my Index and Match function.

IF(ISNUMBER(VALUE(OFFSET(L8,0,-2))),VALUE(OFFSET(L8,0,-2)),OFFSET(L8,0,-2)

If my L8 is '00035', the the above code returns 35 instead of '00035'.

It s evaluating the ISNUMBER to TRUE and returning the value of VALUE(OFFSET(L8,0,-2)) which is 35 instead of OFFSET(L8,0,-2) which is '00035'.

This function works for text values and number values. But, it is not working for numbers with leading 0s.

Please suggest how can we retain the value as 00035 without changing the logic for other numbers or text values.

Thanks in advance.

Thanks for the update.

Attach the excel. I am looking to have number in the same format as given which is '00035'

Thanks.

Not sure if I am doing anything wrong. Lookup doesn't seem to be working for me when I try in a different excel.
Attach the file.