Note: The other languages of the website are Google-translated. Back to English
Registo de cliente  \/ 
x
or
x
Registe-se  \/ 
x

or

Como verificar se a célula contém um dos vários valores no Excel?

Supondo que você tenha uma lista de strings de texto na coluna A, agora, você deseja testar cada célula se ela contém um dos vários valores com base em outro intervalo D2: D7. Se ele contiver algum texto específico em D2: D7, exibirá True, caso contrário, exibirá False conforme a captura de tela a seguir. Neste artigo, falarei sobre como identificar uma célula se ela contiver um dos vários valores em outro intervalo.


Verifique se uma célula contém um dos vários valores de uma lista com fórmulas

Para verificar se o conteúdo de uma célula contém algum dos valores de texto em outro intervalo, as seguintes fórmulas podem ajudá-lo, faça o seguinte:

Insira a fórmula abaixo em uma célula em branco onde deseja localizar o resultado, B2, por exemplo, em seguida, arraste a alça de preenchimento para baixo até as células às quais deseja aplicar esta fórmula e se a célula tiver algum dos valores de texto em outro intervalo específico, se tornará True, caso contrário, se tornará False. Veja a imagem:

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0

Dicas:

1. Se desejar usar “Sim” ou “Não” para indicar o resultado, aplique a seguinte fórmula e obterá o seguinte resultado conforme necessário, consulte a captura de tela:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes","No")

2. Nas fórmulas acima, D2: D7 é o intervalo de dados específico no qual você deseja verificar a célula, e A2 é a célula que você deseja verificar.


Mostra as correspondências se a célula contiver um dos vários valores de uma lista com fórmulas

Às vezes, você pode querer verificar se uma célula contém um valor na lista e, em seguida, retornar esse valor; se vários valores corresponderem, todos os valores correspondentes na lista serão exibidos conforme a captura de tela abaixo. Como você poderia resolver essa tarefa no Excel?

Para exibir todos os valores correspondentes se a célula contiver um do texto específico, aplique a fórmula abaixo:

=TEXTJOIN(", ", TRUE, IF(COUNTIF(A2, "*"&$D$2:$D$7&"*"), $D$2:$D$7, ""))

Importante: Na fórmula acima, D2: D7 é o intervalo de dados específico no qual você deseja verificar a célula, e A2 é a célula que você deseja verificar.

Então aperte Ctrl + Shift + Enter juntas para obter o primeiro resultado e, em seguida, arraste a alça de preenchimento até as células às quais deseja aplicar esta fórmula, veja a captura de tela:

Dicas:

A função TEXTJOIN acima está disponível apenas para Excel 2019 e Office 365, se você tiver versões anteriores do Excel, deve aplicar a fórmula abaixo:

=IFERROR(INDEX($D$2:$D$7, SMALL(IF(COUNTIF($A2, "*"&$D$2:$D$7&"*"), MATCH(ROW($D$2:$D$7), ROW($D$2:$D$7)), ""), COLUMNS($F$1:F1))), "")

Importante: Na fórmula acima, D2: D7 é o intervalo de dados específico no qual você deseja verificar a célula, e A2 é a célula que você deseja verificar.

Então aperte Ctrl + Shift + Enter juntas para obter o primeiro resultado e, em seguida, arraste a célula da fórmula para o lado direito até que a célula em branco seja exibida e, em seguida, continue arrastando a alça de preenchimento para baixo para outras células, e todos os valores correspondentes foram exibidos conforme a captura de tela abaixo:


Destaque as correspondências se a célula contiver um dos vários valores de uma lista com um recurso útil

Se você quiser destacar uma cor de fonte específica para os valores correspondentes, se a célula contiver um dos vários valores de outra lista, nesta seção, apresentarei um recurso fácil, Marcar palavra-chave of Kutools for Excel, com esse utilitário, você pode destacar uma ou mais palavras-chave específicas de uma vez dentro das células.

Observação: Para aplicar estes Marcar palavra-chave recursos, em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique os recursos de forma rápida e fácil.

Depois de instalar Kutools for Excel, faça o seguinte:

1. Clique Kutools > Texto > Marcar palavra-chave, veja a captura de tela:

2. No Marcar palavra-chave caixa de diálogo, execute as seguintes operações:

  • Selecione o intervalo de dados que você deseja destacar os textos correspondentes do Variação caixa de texto;
  • Selecione as células que contêm as palavras-chave que você deseja realçar com base, você também pode inserir as palavras-chave manualmente (separadas por vírgula) no Palavra-chave caixa de texto
  • Por fim, você deve especificar uma cor de fonte para destacar os textos por cheque Marque as cores da palavra-chave opção. 

3. Então clique Ok botão, todos os textos correspondentes foram destacados como a imagem abaixo mostrada:


Mais artigos relativos:

  • Compare duas ou mais strings de texto no Excel
  • Se você quiser comparar duas ou mais strings de texto em uma planilha com ou sem distinção entre maiúsculas e minúsculas, conforme a captura de tela a seguir mostrada, neste artigo, falarei sobre algumas fórmulas úteis para você lidar com essa tarefa no Excel.
  • Se a célula contiver texto, será exibida no Excel
  • Se você tiver uma lista de sequências de texto na coluna A e uma linha de palavras-chave, agora, você precisa verificar se as palavras-chave aparecem na sequência de texto. Se as palavras-chave aparecem na célula, exibindo-a, caso contrário, a célula em branco é exibida conforme a captura de tela a seguir.
  • Encontre e substitua vários valores no Excel
  • Normalmente, o recurso Localizar e Substituir pode ajudá-lo a localizar um texto específico e substituí-lo por outro, mas, às vezes, você pode precisar localizar e substituir vários valores simultaneamente. Por exemplo, para substituir todo o texto "Excel" por "Excel 2019", "Outlook" por "Outlook2019" e assim por diante, conforme a captura de tela abaixo mostrada. Neste artigo, vou apresentar uma fórmula para resolver essa tarefa no Excel.

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Rajeev · 9 months ago
    Multiple search value in a single cell, and return as a Common Value
  • To post as a guest, your comment is unpublished.
    Jerry · 1 years ago
    Is it possible to do this with a partial match of the keywords? This method is only an exact match
  • To post as a guest, your comment is unpublished.
    Margo · 1 years ago
    Hi - What if I needed to Search the Row for the keywords instead of just the cell i.e. A2 - P2 instead of just A2 in this formula? =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes","No")
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Margo,
      To apply this formula to a row data, you just need to replace the single cell reference with the row reference as below:
      =IF(SUMPRODUCT(--ISNUMBER(SEARCH($R$2:$R$6,A2:P2))),"Yes","No")
      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Scott · 1 years ago
    This has been very helpful, but I am just missing the final piece.
    How to I expand IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes","No") to also check against the case sensitivity of the values in the list?
    Thanks in advance
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Scott,
      To check the values with case sensitivity, please apply the below formula:
      =IF(SUMPRODUCT(--ISNUMBER(FIND($D$2:$D$6,A2))),"Yes","No")

      Please try it, thank you!
  • To post as a guest, your comment is unpublished.
    Manish Sharp · 1 years ago
    Perfect!! Complex demand matched by combination of few well know formulas
  • To post as a guest, your comment is unpublished.
    Tony · 1 years ago
    exactly what I was looking for! works perfectly
  • To post as a guest, your comment is unpublished.
    Dd · 2 years ago
    Formula is incorrect. It is showing "true" for any value
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hi, Dd,
      The formula works well in my Excel worksheet, can you give your problem sheet as screenshot here?
      Thank you!
  • To post as a guest, your comment is unpublished.
    m.tahreem.alam@gmail.com · 2 years ago
    Please check
  • To post as a guest, your comment is unpublished.
    m.tahreem.alam@gmail.com · 2 years ago
    Hey,
    I tried this formula, but it is showing TRUE for everything?
  • To post as a guest, your comment is unpublished.
    Nils · 2 years ago
    Would it be possible to change the result of this formula from just being 'TRUE' , 'FALSE', 'Yes', or 'No', into the value of the specific text?


    Thank you
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hi, Nils,
      If you want to use other text to instead the "Yes" or "No", you just need to replace the "Yes", "No" with other texts, please try the below formula:
      =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"your specific text1","your specific text2")

      Please try it, hope it can help you!
      • To post as a guest, your comment is unpublished.
        Alex · 2 years ago
        Jane asked below "Same question; Can we return the text that was matched rather than yes/no?" - also interested in the answer
        • To post as a guest, your comment is unpublished.
          Lorraine · 2 years ago
          =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes,"No")
  • To post as a guest, your comment is unpublished.
    Jane · 2 years ago
    Same question; Can we return the text that was matched rather than yes/no?
  • To post as a guest, your comment is unpublished.
    Emily · 2 years ago
    Any way to return the text that matches in the cell rather than a yes or no?
  • To post as a guest, your comment is unpublished.
    · 2 years ago
    YES, NO 가아닌 메칭되는 값(KTE,remove) 들어있는 값이 보이게 넣을수는 없나요?

    중복되는 값 배열일 경우 배열로 B열 C열......... 기재는 가능한가요
  • To post as a guest, your comment is unpublished.
    carla · 2 years ago
    used =SUMPRODUCT(--ISNUMBER(SEARCH(list,A2))) along with conditional formatting to help compare the values of one column with another. works great with excel 2007. been racking my brain for days.
  • To post as a guest, your comment is unpublished.
    shl · 2 years ago
    유용한 자료 감사합니다!
  • To post as a guest, your comment is unpublished.
    Reece · 2 years ago
    Thanks heaps, this worked.
  • To post as a guest, your comment is unpublished.
    goldwater · 3 years ago
    감사합니다 정말 진심으로 감사드립니다
    한달여간을 네이버에서만 찾다가 구글을 통해 찾으니 이렇게 바로 정확한 정보가 뜨네요 정말 감사드립니다 !
  • To post as a guest, your comment is unpublished.
    sammy · 3 years ago
    hi
    see attached file, im trying to find out if any of the ingredients from column D exists in column A and display whatever ingredient thats found in column B, is this possible?
    • To post as a guest, your comment is unpublished.
      sammy · 3 years ago
      first attachment didnt upload, see below attachment
  • To post as a guest, your comment is unpublished.
    alexezorsky@gmail.com · 3 years ago
    How would I make this work if there were blank cells in the D column?
  • To post as a guest, your comment is unpublished.
    thachho18@hotmail.com · 4 years ago
    Very useful fomula.
    Can we grab that value found from the search? (i.e. I want to check if the address field containing the 4 digit post code within the range, if yes then show value of the 4 digits post code.)
    David
    • To post as a guest, your comment is unpublished.
      skyyang · 4 years ago
      Hi, David,
      My be this array formula can help you:
      =IF(SUMPRODUCT(IF(ISNUMBER(SEARCH($D$2:$D$4,A2)),ROW($D$2:$D$4)-ROW($D$2)+1,0))>0,INDEX($D$2:$D$4,SUMPRODUCT(IF(ISNUMBER(SEARCH($D$2:$D$4,A2)),ROW($D$2:$D$4)-ROW($D$2)+1,0))))
      After pasting the formula, please press Shift + Ctrl + Enter keys together.
      This formula is somewhat complex, please change the cell references to your need.
      Please try it, hope it can help you!
      • To post as a guest, your comment is unpublished.
        Bruno Pace · 2 years ago
        skyyang,

        I don't know if you ever gonna read this, but thank you so much!

        You are a Genius!!!
      • To post as a guest, your comment is unpublished.
        Yogesh · 2 years ago
        Can the list in Column D be of 15000 words and still the formula remains same as mentioned above? I am getting expected results with just few values but the moment list jumps to thousands, it all shows #REF.
      • To post as a guest, your comment is unpublished.
        Tina M Morris · 2 years ago
        Myself and another gal have been trying to figure this one out for 2 days for a data problem we have to resolve. Thank you for this expression. It is exactly what we needed.
      • To post as a guest, your comment is unpublished.
        sammy · 3 years ago
        i have a list of ingredients in column A, each cell has the whole ingredients of a product so its not just one word, in column D i have a the list of ingredients im trying to find within column A, how do i that? this formula doesnt seem to work 100% for this
        thanks
      • To post as a guest, your comment is unpublished.
        sam · 3 years ago
        does this formula work when there is a couple of words in the lookup cells and couple of words in the search in cells? basically i have lists of ingredients and want to locate some specific ingredient from within those list and the specific ingredient might be more than one word
      • To post as a guest, your comment is unpublished.
        alexezorsky@gmail.com · 3 years ago
        This is amazing! One big issue for me however: What if there are blank cells in my "search for" D2:D4 list. For me it ruins the formula and I just get "NUM" in a few places. THANKS!
      • To post as a guest, your comment is unpublished.
        thachho18@hotmail.com · 4 years ago
        Amazing, it works exactly the way that I needed. Thank you Champs.
        • To post as a guest, your comment is unpublished.
          skyyang · 4 years ago
          Glad to help you!
          • To post as a guest, your comment is unpublished.
            geenie · 3 years ago
            hi, would you please explain how you got that function above? I'm trying to do the same thing but cannot seem to change the cell references correctly.
            • To post as a guest, your comment is unpublished.
              Jon · 3 years ago
              Yes please, I need this too. I can't see why $D$2 is locked?
  • To post as a guest, your comment is unpublished.
    Dev · 4 years ago
    What if the "specific text" column contains phrases?
    For example
    "remove duplicate"
    "convert text"
    "find across"

    how to check then?
    • To post as a guest, your comment is unpublished.
      Taru · 1 years ago
      Instead of True or False I want to print the name of the found text, how can I do that?
      • To post as a guest, your comment is unpublished.
        Majid · 1 years ago
        check the formula given by skyyang... That works well