• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Results based on matching first 4 characters in a text string

I am currently using the index match formula below:

=IFERROR(INDEX('M:\[BookProduction_12DEC2013.xlsm]Charts_Pub'!$S$2:$S$3633,MATCH(A2,'M:\[BookProduction_12DEC2013.xlsm]Charts_Pub'!$M$2:$M$3633,0)),"—")

I would like to modify it to match only the first 4 characters of the text string in A2 instead of the entire text string in the cell. The first 4 characters are a mix of alpha-numeric characters, and always have an underscore following them. If there is no match, I would like the result to say "Not in book". See examples below of text string:

CCA6_XXX_32_0_1.pdf
CYVR_POYR_NN75_32_0_1.pdf
CYSJ_IPSJ_DEMR_32_0_1.pdf
CYWK_IPSJ_DEMR_36_0_1.pdf

I've also included a sample file to illustrate what I'm looking for.

Thanks,
Andrea
EE-match-partial-Oct25.xlsx
0
Andreamary
Asked:
Andreamary
  • 3
  • 2
  • 2
4 Solutions
 
NBVCCommented:
Try:

=IFERROR(INDEX(Data!$D$2:$D$33,MATCH(LEFT(A2,4),Data!$B$2:$B$33,0)),"Not in book")



also, can you please close off any other threads you have open if the issues are resolved.  Thanks.
0
 
barry houdiniCommented:
Given your example you could also use VLOOKUP, i.e.

=IFERROR(VLOOKUP(LEFT(A2,4),Data!$B$2:$D$33,3,0),"Not in book")

regards, barry
0
 
AndreamaryAuthor Commented:
The first solution provided worked, thanks, so I will follow up and close this post. In trying the second solution, I adjusted it for my spreadsheet, and in doing so had to change "3,0), "Not in book")" to "20,0), "Not in book")", and got "0" as a result. In experimenting, it appears that going beyond "9,0), "Not in book")" causes it to fail. Is that a limitation of the VLOOKUP?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
NBVCCommented:
You have to make sure that the lookup table:  Data!$B$2:$D$33 starts from the column you are looking up the items in, and the table must end at or after the column you are looking to extract from.  The column Index Number (4th parameter in the VLOOKUP function) must be the count of columns starting from your lookup column to the column you are extracting from.

This is why the INDEX/MATCH is perhaps a good choice.  You just index and match the columns of interest without having to worry about column counts, or if column of the lookup column is to the left or right of the column to extract from.
0
 
barry houdiniCommented:
No there's no such limitation on VLOOKUP - if you specify 20 then the result will be from the 20th column of the specified "lookup array" - what formula did you try?

regards, barry
0
 
AndreamaryAuthor Commented:
Thanks for your patience and explanations. I went back to the drawing board and both formulas work perfectly!
0
 
AndreamaryAuthor Commented:
Since both solutions worked, and you both provided helpful comments, I decided to distribute the points. The formula chosen as the "Best solution" was the shorter one of the two. I hope I've handled this to everyone's satisfaction. Thanks again for your prompt responses and great solutions!
Andrea
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now