Concat & Index mixed in a formula help

[gjjh25]

I am trying to bring together some cell items using concat, but am having a problem because I think I need to incorporate Index and Match in it, but I don't know how to do it.

If someone could help please it would be much appreciated.

In the enclosed file I want it to be on line 11, and copyable down.

I am also trying to format a text item, within the concat formula too, but can only get it to show 0 rather than S, I have cells format as this and it works, but I have copied the formating into the formula and it doesn't?

So on line 11 with the formula the sample of how i want it to look is on line 16.

So for the 1.50 - 1.75 part, i want it to look at the manhole refernve on tab surface manhole and then look how deep it is on surface manhole depth range tab an bring forward its depth from lines 9 & 10

Finally on the surface manhole tab the manhole has 1 hydrobrkae, if this is the case I want this bringing into the description, if it is not then nothing should show. e.g if there is a 1 in the box it is needed if it is empty then nothing.