If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Index/Matching in combination w/Right()
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.. |
#2
|
|||
|
|||
Index/Matching in combination w/Right()
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),MO D(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) -- If this post helps click Yes --------------- Jacob Skaria "ShagNasty" wrote: 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.. |
#3
|
|||
|
|||
Index/Matching in combination w/Right()
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) "ShagNasty" wrote: 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.. |
Thread Tools | |
Display Modes | |
|
|