Match part of cell from another column, return corresponding text

I've been trying to get this to work for far too long, tearing my hair out now so looking for some help...so help me...please!

Using 2 reports I pull off of our finance system, I get 2 sheets of data, which I've pasted into one document.

The attached are these reports on a much smaller scale - sheets 'Overheads' and 'Active Grants'.

What I'm trying to do is -

Firstly - on the 'Overheads' sheet, I need to find a formula for column H, to pull the name of the Principal Investigator, from the 'Active Grants' sheet. The formula doesn't currently work, because column F contains more than the text I'm searching for, and I don't know how to work around that?

Secondly - Is there a way to pull the Research Job Code that corresponds, and enter that in column I?

Finally - The name of the 'Principal Investigator' (column B) on 'Active Grants' tab can be in various formats (Dr Black, Ian Black, Dr Ian Black, Black I Dr), is there any kind of formula to standardise that?

Thanks for any help you can give me on this one...I'm so lost with it!

That's great! Looks like exactly what I want. Thank you!

With regards to my last query, I'll try to explain it a little better -

The report I pull that gives me the 'Active Grants' sheet comes from a Research Grants system, where data is input by a central Research department. When they input the name of the principal investigator, they don't use a standard format for it. So it can be entered as Dr Black, Dr Malcolm Black, Malcolm Black, Black M Dr. This means when I create a pivot table, I get separate entries in there for each of the different methods of entering the name. I'd like for them to be joined up, but not sure there's a way of doing it.

Does that make sense?

Nah, unfortunately not. I can work with it the way it is though, while not perfect - it's close enough!

However, I have found one more problem -

Is there a way to use the formula you gave me, for Research Job Code on 'Overheads' tab - =IFERROR(LOOKUP(1000,SEARCH('Active Grants'!$A$2:$A$10,F2),'Active Grants'!$A$2:$A$10),"") - so that I can add more lines to the spreadsheet without having to update the formula? The way it is, it only checks between A2 and A10, I tried to change it to $A$2:$A$10000 but that just gave me '0' for all answers. Do I have to update the formula, and enter the bottom row number where it has 10?

Hi

Here an array formula solution to find Research Job Code

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Or this to a regular formula

Formula:

Please Login or Register  to view this content.