Using text functions in vlookups

[Bombaye]

I am having difficulty getting a formula to work.

I am trying to use 2 lookup tables in the same formula and in both the mid or right function brings back a value if I press F9 but the complete formula isn't working.
Sheet Name is a function that has been written by someone that returns the name of the tab.

This is what it currently looks like:

="PR = "&LEFT(Sheet_Name($A$1),2)&", BS = "&VLOOKUP(MID(Sheet_Name($A$1),2,2),Lookup_Table2,2,0)&", BT = "&VLOOKUP(RIGHT(Sheet_Name($A$1),2),Lookup_Table1,2,0)

An example of a tab name is A1_G_3 so PR=A1, BS= G, BT=3

 

[GlennUK]

You say that you've tested the MID or RIGHT using F9. What about the VLOOKUPs? Test those in seperate cells like:

=VLOOKUP(MID(Sheet_Name($A$1),2,2),Lookup_Table2,2,0)
=VLOOKUP(RIGHT(Sheet_Name($A$1),2),Lookup_Table1,2,0)

 

[Bombaye]

The second works but the first doesn't.

What complicates it more is that the 3 sections aren't always the same size, PR is either 2 or 6 characters, and BS and BT are 1 or 2 with the sections separated by an underscore. Is there a way I could combine it in one big formula so I don't have to adapt it for each tab?

At the moment I've inserted underscores into the lookup tables where necessary to make BS and BT all the same length.

 

[cjcobra]

incorporate a FIND("_",Table,1) and it will find what text digit the underscore is. Then you can take a MID starting at your FIND number. Take your MID number of digits out to 10 or so and also put a TRIM to take off the trailing spaces. Basically like TRIM(MID(FIND,"_",Table,1)...

 

[Bombaye]

incorporate a FIND("_",Table,1) and it will find what text digit the underscore is. Then you can take a MID starting at your FIND number. Take your MID number of digits out to 10 or so and also put a TRIM to take off the trailing spaces. Basically like TRIM(MID(FIND,"_",Table,1)...

That sounds great, but I've never used either of those formulae and it's fairly complex, please could you suggest what mine should be.