Find the sub string in column and copy the cell next to the substring

Hello Experts,
I'm not an expert with VBA, so kindly help with the issue i have.

Consider i have a table,
ColumnA--ColumnB--ColumnC -- ColumnD
Orange -- fruit -- Vegetable -- blank
Pineapple --juice -- OrangeJuice --blank

I need to search for 'orange' substring in columnC until the end of the columnC and if found copy the cell next to orange i.e columnB.1 (fruit) to ColumnD.1 else copy 'Not Available'.

I tried with excel formula but works only for one cell.

=IF( (LEN(A2) <=LEN(C2)), IF( (MID(A2,1,LEN(A2)) = MID(C2,1,LEN(A2)) ),B2))

Thanks in advance,

Regards,
Shrujan