Ive used this formula in the past with help from Donkeyote - =SUBSTITUTE(IF(RIGHT($D2,8)="Rear Kit",Sheet3!$A$1,Sheet2!$A$1),"NAMEOFCAR",Sheet4!$A2) for a different project. I thought something like this would work but instead of substituting text from another sheet it would substitute a searched result from column D.

And the search would specify that if the text in column D says 50cm then (PRODUCTWIDTH) is 50cm, if column D says 61cm then (PRODUCTWIDTH) is 61cm, column D says 76cm then (PRODUCTWIDTH) is 76cm, column D says 91cm then (PRODUCTWIDTH) is 91cm, column D says 100cm then (PRODUCTWIDTH) is 100cm, if column D says 117cm then (PRODUCTWIDTH) is 117cm, column D says 122cm then (PRODUCTWIDTH) is 122cm, column D says 152cm then (PRODUCTWIDTH) is 152cm

Then for the length if column D has the text 1m then (PRODUCTLENGTH) is 1m, Then for the length if column D has the text 2m then (PRODUCTLENGTH) is 2m, Then for the length if column D has the text 3m then (PRODUCTLENGTH) is 3m, Then for the length if column D has the text 4m then (PRODUCTLENGTH) is 4m all the way up to 30m in 1 m increments.