Solved

Results based on matching first 4 characters in a text string

Posted on 2013-10-25
7
148 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 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now