asked on

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

SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

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?

SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

Thanks for your patience and explanations. I went back to the drawing board and both formulas work perfectly!

Andreamary

ASKER

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