Variabel text in MID. VLOOKUP?

Hi,

I´m trying to do the following:
I write a name in A1. The formula searches in C3-E4 to find that name. If it finds it it returns the initials between the paranthesis.

I can´t, for different reasons, separate name and initials in different cells and just use VLOOKUP. That is, I can´t change the way the document looks. (The image is just a simplified version of the big document who we send out externally).

This far I have just used:
=MID(C3; SEARCH("(";C3)+1; SEARCH(")"; C3)-SEARCH("("; C3)-1)

Above doesn´t work since I want to be able to change name in A1 and therefor get different initials in B1. I tried exchanging C3 with VLOOKUP(A1; C3:E4; 1; TRUE) but it doesnt gave me a correct value back. (and probably is completely wrong but I´m kind of stuck here..)

So question is: What formula can I use to be able to do this?

Many thanks!
//Erik

excel.JPG

Thank you for a swift reply!

I tried to alter your formula a bit to suit my document. The cell where I change the name is H66 och and the search area is H80:Q87.
One thing to notice is that every second column between H and Q are empty due to design reasons in the document. Don´t know if it matters.

Unfortunately I get an error when inserting the below formula.. Don´t know whats wrong. Do you see it?

=MID(INDEX($H$80:$Q$87;1;MIN(IF(LEFT($H$80:$Q$87;FIND("(";$H$80:$Q$87)-2)=H66;COLUMN($H$80:$Q$87)-COLUMN($H$80)+1;99^99)));FIND("(";INDEX($H$80:$Q$87;1;MIN(IF(LEFT($H$80:$Q$87;FIND("(";$H$80:$Q$87)-2)=H66;COLUMN($H$80:$Q$87)-COLUMN($H$80)+1;99^99))))+1;2)

Best Regards
Erik

Still an error I´m afraid. Don´t know if I can share the error code with you somehow?

Many Thanks

@leelnich:
Your formula worked (almost). One problem is that when I enter the following:
Enter: CompanyX

Then the formula search the area with all the company names - find "CompanyX (BBB/K2)" end returns just "K2".. I want it to return "BBB/K2".. The length between the parenthesis varies.. It can be "NR", "BBB/A-2", (AAA/K1) and so on..

Thank you guys!