Vlookup needed to give value lookup value is a string within a cell.

Hello there

I am trying to extract information from Column B to state the value after the space so in the instance of C7 it should say "JHD/Southampton Resourcing" The problem I am having is that I can't seem to figure out how to create an IF function that will resolve as true (probably due to text and number issues)

I need a lookup to just take the first one or two left hand characters from the cell and return the information minus the task number. So we can filter it with auto filter later.

I have 8 sheets with this information that I need to go through. This is an important task I can't afford to do it manually as there is too much propensity for human error. Whilst I was creating this example sheet I made a mistake in the manual entry in column D, which is corrected now

The data is sourced from some other formulas, which are working perfectly.

Combined with that if you look in cell B10 you will see that there is one instance of the explanation for task 3. I need it to say "JHD/Resourcing" on both occasions (cells D11:D12)

If anyone knows an array formula that can be entered into column C that can achieve what I have detailed columns D + E I would be grateful.

I tried =LEFT(B7,1) which resolves as 1 and the value in cell A7 is 1. But for some reason when I try =LEFT(B7,1)=A7 it is resolving as false which is preventing me from advancing what I want to do. (see attached sheet)

Please let me know if you need further description.

I'll try it but it doesnt solve the lookup issue I need index match and something to then disqualify the parts of the cell I don't need.