?
Solved

Results based on matching first 4 characters in a text string

Posted on 2013-10-25
7
Medium Priority
?
186 Views
Last Modified: 2013-10-26
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
Comment
Question by:Andreamary
  • 3
  • 2
  • 2
7 Comments
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 1000 total points
ID: 39600984
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 1000 total points
ID: 39600993
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
 

Author Comment

by:Andreamary
ID: 39601393
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 1000 total points
ID: 39601497
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
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 1000 total points
ID: 39601505
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
 

Author Comment

by:Andreamary
ID: 39602714
Thanks for your patience and explanations. I went back to the drawing board and both formulas work perfectly!
0
 

Author Closing Comment

by:Andreamary
ID: 39602723
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question