Function/Array for matching data from tree column

Hi Experts,
I have four columns that requires function or array for calculations
In the past, I have used data filters, I am sure some function or array is possible
please see attached file
Thanks in Advance

Karnik

Hi Bo_RY

Thanks for solution it is simple and works, I was attempting using IF function and was getting bit complicated
Thanks!
Karnik