Index/Matching in combination w/Right()

[ShagNasty]

I have a spreadsheet worksheet used as “lookup†table where Col A2:A65 =
JCodes and Col B2:B65 = JDesc. Job Code is a “general†field with JCodes
formatted as 004455, 000333, etc. (two leading 0’s with each JCode). On
another worksheet I enter the JCode to get the JDesc using the six character
JCode (=INDEX(JobCodes!$B$2:$B$65,MATCH(A13,JobCodes!$A$2:$A$65,0)). I want
to use just the last 4 characters to return the JDesc (maybe using the
“right()†function). Anywho, I haven’t had much luck except to get error or
value messages. Please help..

 

[Jacob Skaria]

If A2:A65 if formatted as number with 2 leading zeroes (always), you will be
able to do the lookup directly.

=INDEX(JobCodes!$B$2:$B$65,MATCH(MOD(A13,10000),MOD(JobCodes!$B$2:$B$65),0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in the Formula Bar you can notice the curly braces at both ends
"{=<formula>}"

OR

if you have formatted that as text use the below(assuming leading zeros
always)
=VLOOKUP(TEXT(A3,"000000"),JobCodes!$B$2:$B$65,2)

 

[bapeltzer]

I'd use a function within the MATCH to force your 4-digit code to match the
codes in your table... So if your table's codes are 6-digit text strings,
then instead of matching A13, I'd match "00" & A13. (I'm guessing that the
codes are six digits of text, rather than numbers formatted as "000000", or
you probably wouldn't be having an issue).
Also, if the 'key value' you're looking up is in colum A and the value
you're returning in column B, you could use a vlookup rather than
index/match:
=vlookup("00"&A13,JobCodes!$A$2:$B$65,2,0)