Excel 2013 Procura por substring na coluna usando a coluna de pesquisa e retorna o valor de acompanhamento

Question

Eu tenho 2 folhas:

sheet1 contém 2 colunas da seguinte forma:

columnA   columnB
AB        Texas
BC        California
DE        Virginia

sheet2 contém 1 colunas da seguinte forma:

columnA
AB_documentOne.pdf   
BC_documentTwo.pdf   
DE_documentThree.pdf 
AB_documentFour.pdf  
AB_documentFive.pdf  
DE_documentSix.pdf  

Na coluna adjacente "columnB" na planilha2, gostaria de extrair os valores corretos da coluna B na planilha1.

Resultado desejado:

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

Eu tentei:

=INDEX(sheet1!B:B,MATCH(sheet1!A:A,sheet2!A:A,0))

Como estou usando columnA da sheet1 como uma pesquisa de substring para columnA em sheet2, isso produzirá um erro.

Versão do Excel: 2013

Obrigado pela sua ajuda.

    

Answer 1

Consegui o seguinte para trabalhar com um mod LEFT (string, 2) na sua pesquisa, alternar a ordem dos parâmetros de correspondência e alternar A: A para uma célula específica, mas ancorar a coluna para que você possa Copie a coluna sem problemas:

=INDEX(Sheet1!B:B,MATCH(LEFT(Sheet2!$A1,2),Sheet1!A:A,0))

EDITAROCOMENTÁRIODEACOMPANHAMENTO:

Paraencontrarsuasequênciadecaracteresemqualquerlugardotextocompleto,aúnicamaneiraqueconseguifazeréusarfórmulasdematriz(énecessárioinseri-laspressionandoCTRL+SHIFT+ENTER)eusandoumtruqueCOUNTIF()euencontrei aqui para encontrar primeiro o índice de onde a string de destino começa a corresponder ao texto completo.

O COUNTIF () descobre quais de suas strings de destino foram encontradas (na verdade, fornece uma contagem de correspondências), então use o índice de qual string tem 1 match usando MATCH (), e então seu INDEX () pega o nome do estado correspondente que acompanha a correspondência encontrada.

=INDEX(Sheet1!B:B,MATCH(1,COUNTIF(A1,"*"&Sheet1!A:A&"*"),0))