I have 2 sheets:

sheet1 contains 2 columns as follows:

columnA   columnB
AB        Texas
BC        California
DE        Virginia

sheet2 contains 1 columns as follows:


In adjacent column "columnB" in sheet2, I would to extract the correct values from columnB in sheet1.

Desired result:

columnA               columnB
AB_documentOne.pdf    Texas
BC_documentTwo.pdf    California
DE_documentThree.pdf  Virginia
AB_documentFour.pdf   Texas
AB_documentFive.pdf   Texas
DE_documentSix.pdf    Virginia

I tried:


Since I am using columnA from sheet1 as a substring lookup for columnA in sheet2, this will produce an error.

Excel version: 2013

Thanks for your help.

  • There are cases where columnA in sheet2 will not have the lookup value from columnA in sheet1 as a prefix, e.g. H103_AB_documentSeven.pdf – Sconny Oct 7 '15 at 21:22

I was able to get the following to work with a LEFT(string,2) mod to your lookup, switch the order of the match parameters, and switch A:A to a specific cell, but anchor the column so you can copy it down the column without issue:


enter image description here


To find your target string anywhere in the full text, the only way I have been able to make it work is using array formulas (must enter them by pressing CTRL+SHIFT+ENTER) and using a COUNTIF() trick I found here for first finding the index of where the target string starts matching the full text.

The COUNTIF() finds which of your target strings is found (actually it gives you a count of matches), then use the index of which string has 1 match by using MATCH(), and then your INDEX() grabs the corresponding state name that goes along with the match that was found.

| improve this answer | |
  • Great success!! That worked perfectly. Much appreciated! – Sconny Oct 7 '15 at 22:49
  • How would it work if the substring is not always at the beginning or end, but somewhere in the middle? – Sconny Oct 8 '15 at 0:03
  • @Sconny Give that edit a shot – panhandel Oct 8 '15 at 2:55
  • I absolutely tried before asking. :) – Sconny Oct 8 '15 at 3:05
  • 1
    That worked! Excellent. – Sconny Oct 8 '15 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.