Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Results based on matching first 4 characters in a text string

Posted on 2013-10-25
7
Medium Priority
?
184 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

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.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€Ĥ
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

636 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