Breaking down cell value for INDEX/MATCH

Cell G10 will ALWAYS have a value under the following format:

XX-14-##, XXX-15-##, XXXX-14-##

Where the first letters (they will always be letters) BEFORE the hypen ("-"), represent a Contractor code

The 2 numbers between the hyphens represent the year (14 = 2014)

The LAST 2 numbers (the ones AFTER THE HYPHEN) represent the district.

Based on the value of G10, how can I have J5 give me the district (I have a separate tab that lists the districts: column D is district number, column E is District Name (what I want), column F is Region name)?

Based on G10, How can I have J6 give me the region?

Based on G10, How can I have J9 give me the contractor name(the separate tab that I have also lists the contractors: column A is the contractor code, column B is the Contractor name.)?
Note: the code SCE will NEVER APPEAR, but instead MW, ME, NC, DE, OR, RLN, RLS, SJC, SJOA.

If an upload is needed, let me know and I'll put it up.

Daffodil, I see from your upload it works, but not when I transcribe over the formula to my spreadsheet.

Let me upload mine, so maybe you can see why it doesn't migrate over nicely.

Just as an example, there are 10 different tab examples that show how J5, J6, and J9 are supposed to look like. My vlookup range is on "Reference List" tab.

By the way, loved your Lord of the Ring Humor. lol

Originally Posted by daffodil11

Ahh, I remember this attachment.

I worked for a couple of hours yesterday trying to come up with some code to automate your new tab generation.

Oh, how interesting. Did you get anything working with that?

Originally Posted by daffodil11

Oh the formulas all look good to me, your template is just generating an error because there's no code in G10.

Oh yeah huh... duh!

Originally Posted by daffodil11

To prevent the error, you could encapsulate each formula in =IFERROR(formula,"") which will create a blank cell.

Thanks. Will do.

Daffodil, it's almost there. I noticed that on J9 (the vendor/contractor), when G10 starts with MW, ME, OR, NC, DE, RLN, RLS, SJOA, or SJC it doesn't populate J9 as SCE, just like the example sheets show them. (See Reference List tab)
G10 will never start with SCE... only the region code (MW, ME, OR, NC, DE, RLN, RLS, SJOA), but I do want it to say SCE on J9.

Other than that, it's getting better.

Daffodil... that did it!

Whatever you can do for that OTHER issue with the check sheets that would be great... but not as great as the way you name the excel files.

If you do still feel its out of your league... check that forum thread... there's been some headway, but it's not 100% yet. http://www.excelforum.com/excel-prog...ed-sheets.html

Daffodil (or anyone else), the working formula needs a minor tweaking due to work scope changes at work.

The following G10 cell value of MW-15-44 would be broken down by these 2 formulas to give me its corresponding VLOOKUP value:

J5=IFERROR(VLOOKUP(RIGHT($G$10,2)*1,'Reference List'!$D$1:$F$37,2,0),"") WHICH WOULD GET THE 44 FROM G10 and use it for a VLOOKUP.
J6=IFERROR(VLOOKUP(RIGHT($G$10,2)*1,'Reference List'!$D$1:$F$37,3,0),"") WHICH WOULD GET THE MW FROM G10 and use it for a VLOOKUP.

Now though, NEW WORK can make the G10 have a -TR at the end, so G10 = MW-15-44-TR

How can I have my J5 & J6 formulas tweaked to look at:
J5 - Look at the 2 numbers AFTER THE second dash "-"
J6 - Look for all the characters BEFORE the first dash "-"

right now it's reading it from the right of G10, but with values such as MW-15-44-TR coming along (the "-TR" being new), now using RIGHT excel formula won't cut it.

Thanks again to you Daffodil, and to anyone else who can help.

If anyone has anything, that would be great :-)

try

=LEFT(G10,2)... MW

=MID(G10,7,2) .... 44

Originally Posted by JohnTopley

try

=LEFT(G10,2)... MW

=MID(G10,7,2) .... 44

John... that worked!!!

I substitued J5=IFERROR(VLOOKUP(RIGHT($G$10,2)*1,'Reference List'!$D$1:$F$37,2,0),"") with what you gave me to get

J5=IFERROR(VLOOKUP(MID(G10,7,2)*1,'Reference List'!$D$1:$F$37,3,0),"") and it worked perfectly!!

Same worked for J6.

What is MID?

MID function enables to look for text starting at a given position and for a stated length.

A1 ="ABCDEFG"

=MID(A1,4,2) will return "DE" i.e. start at position 4 for length 2

John... I used it and I got an error.

Some logs start with 2 characters

MW-15-32
ME-15-22
NC-15-39

Others have MORE THAN 2 CHARACTRES before the first dash. Ex:
HWI-15-32
HTE-15-47
WEISS-15-44
HTE-15-47-TR

Having this said, I stumbled upon the scenario where I used the log HWI-15-32. Since before the first dash "-" I have 3 characters, the MID formula you gave me won't work. It will only work if BEFORE THE FIRST DASH it is 2 characters.

How can the formula be changed so that it reads ALL THE CHARACTERS BEFORE THE FIRST DASH?

I would want J5 TO BE...

HWI-15-44 = HWI
MW-15-44 = MW
ASPL-15-32-TR = ASPL
NC-15-39-TR = NC
ME-15-34 = ME

Pretty much anything that's BEFORE the first dash.

Originally Posted by JPWRana

How can the formula be changed so that it reads ALL THE CHARACTERS BEFORE THE FIRST DASH?

Try this:

=LEFT(G10,FIND("-",G10)-1)

Hope this helps.

Pete