Solved

Results based on matching first 4 characters in a text string

Posted on 2013-10-25
7
175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 250 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 250 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 250 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 250 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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

696 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