Como criar uma fórmula de pesquisa do Excel com vários critérios

o que saber

• Primeiro, crie uma função INDEX e, em seguida, inicie a função MATCH aninhada inserindo o argumento Lookup_value.
• Em seguida, adicione o argumento Lookup_array seguido pelo argumento Match_type e especifique o intervalo de colunas.
• Em seguida, transforme a função aninhada em uma fórmula de matriz pressionando Ctrl + Shift + Enter. Por fim, adicione os termos de pesquisa à planilha.

Este artigo explica como criar uma fórmula de pesquisa que usa vários critérios no Excel para localizar informações em um banco de dados ou tabela de dados usando uma fórmula de matriz. A fórmula de matriz envolve o aninhamento da função MATCH dentro da função INDEX. As informações abrangem Excel para Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 e Excel para Mac.

Siga junto com o tutorial

Para seguir as etapas deste tutorial, insira os dados de amostra nas células a seguir, conforme mostrado na imagem abaixo. As linhas 3 e 4 são deixadas em branco para acomodar a fórmula de matriz criada durante este tutorial. (Observe que este tutorial não inclui a formatação vista na imagem.)

• Insira o intervalo superior de dados nas células D1 a F2.
• Insira o segundo intervalo nas células D5 a F11.

Criar uma função ÍNDICE no Excel

A função ÍNDICE é uma das poucas funções no Excel que possui vários formulários. A função tem um formulário de matriz e um formulário de referência. O formulário de matriz retorna os dados de um banco de dados ou tabela de dados. O Formulário de Referência fornece a referência da célula ou localização dos dados na tabela.

Neste tutorial, o formulário de matriz é usado para localizar o nome do fornecedor de widgets de titânio, em vez da referência de célula a esse fornecedor no banco de dados.

Siga estas etapas para criar a função INDEX:

1. Selecione a célula F3 para torná-la a célula ativa. Esta célula é onde a função aninhada será inserida.
2. Acesse Fórmulas.

   

3. Escolha Lookup & Reference para abrir a lista suspensa de funções.
4. Selecione ÍNDICE para abrir a caixa de diálogo Selecionar Argumentos.
5. Escolha array,row_num,column_num.
6. Selecione OK para abrir a caixa de diálogo Argumentos da função. No Excel para Mac, o Construtor de Fórmulas é aberto.
7. Coloque o cursor na caixa de texto Array.
8. Realce as células D6 a F11 na planilha para inserir o intervalo na caixa de diálogo.
   Deixe a caixa de diálogo Argumentos da função aberta. A fórmula não está terminada. Você completará a fórmula nas instruções abaixo.
   

Inicie a função MATCH aninhada

Ao aninhar uma função dentro de outra, não é possível abrir o construtor de fórmulas da segunda função, ou aninhada, para inserir os argumentos necessários. A função aninhada deve ser inserida como um dos argumentos da primeira função.

Ao inserir funções manualmente, os argumentos da função são separados uns dos outros por uma vírgula.

A primeira etapa para inserir a função MATCH aninhada é inserir o argumento Lookup_value. O Lookup_value é o local ou referência de célula para o termo de pesquisa a ser correspondido no banco de dados.

O Lookup_value aceita apenas um critério ou termo de pesquisa. Para pesquisar vários critérios, estenda o Lookup_value concatenando ou unindo duas ou mais referências de célula usando o símbolo de e comercial (&).

1. Na caixa de diálogo Function Arguments, coloque o cursor na caixa de texto Row_num.
2. Digite CORRESPONDENTE(.
3. Selecione a célula D3 para inserir essa referência de célula na caixa de diálogo.
4. Insira & (o e comercial) após a referência de célula D3 para adicionar uma segunda referência de célula.
5. Selecione a célula E3 para inserir a segunda referência de célula.
6. Digite , (uma vírgula) após a referência de célula E3 para completar a entrada do argumento Lookup_value da função MATCH.

   
   Na última etapa do tutorial, os Lookup_values ​​serão inseridos nas células D3 e E3 da planilha.

Conclua a função MATCH aninhada

Esta etapa aborda a adição do argumento Lookup_array para a função MATCH aninhada. O Lookup_array é o intervalo de células que a função MATCH pesquisa para encontrar o argumento Lookup_value adicionado na etapa anterior do tutorial.

Como dois campos de pesquisa foram identificados no argumento Lookup_array, o mesmo deve ser feito para o Lookup_array. A função CORRESP pesquisa apenas uma matriz para cada termo especificado. Para inserir várias matrizes, use o e comercial para concatenar as matrizes.

1. Coloque o cursor no final dos dados na caixa de texto Row_num. O cursor aparece após a vírgula no final da entrada atual.
2. Destaque as células D6 a D11 na planilha para inserir o intervalo. Esse intervalo é o primeiro array que a função procura.
3. Insira & (um e comercial) após a célula referenciar D6:D11. Este símbolo faz com que a função pesquise duas matrizes.
4. Realce as células E6 a E11 na planilha para inserir o intervalo. Esse intervalo é o segundo array que a função pesquisa.
5. Digite , (uma vírgula) após a referência de célula E3 para completar a entrada do argumento Lookup_array da função MATCH.

   

6. Deixe a caixa de diálogo aberta para a próxima etapa do tutorial.

Adicione o argumento de tipo MATCH

O terceiro e último argumento da função MATCH é o argumento Match_type. Este argumento informa ao Excel como corresponder o Lookup_value com valores no Lookup_array. As opções disponíveis são 1, 0 ou -1.

Este argumento é opcional. Se for omitido, a função usa o valor padrão de 1.

• Se Match_type = 1 ou for omitido, MATCH localizará o maior valor menor ou igual a Lookup_value. Os dados Lookup_array devem ser classificados em ordem crescente.
• Se Match_type = 0, MATCH localiza o primeiro valor que é igual ao Lookup_value. Os dados Lookup_array podem ser classificados em qualquer ordem.
• Se Match_type = -1, MATCH localizará o menor valor maior ou igual a Lookup_value. Os dados Lookup_array devem ser classificados em ordem decrescente.

Insira estas etapas após a vírgula inserida na etapa anterior na linha Row_num na função INDEX:

1. Digite 0 (um zero) após a vírgula na caixa de texto Row_num. Esse número faz com que a função aninhada retorne correspondências exatas aos termos inseridos nas células D3 e E3.
2. Insira ) (um colchete de fechamento) para concluir a função CORRESP.

   

3. Deixe a caixa de diálogo aberta para a próxima etapa do tutorial.

Conclua a função INDEX

A função CORRESP está concluída. É hora de ir para a caixa de texto Column_num da caixa de diálogo e inserir o último argumento para a função INDEX. Este argumento informa ao Excel que o número da coluna está no intervalo D6 a F11. Este intervalo é onde ele encontra as informações retornadas pela função. Neste caso, um fornecedor de widgets de titânio.

1. Coloque o cursor na caixa de texto Column_num.
2. Digite 3 (o número três). Esse número informa à fórmula para procurar dados na terceira coluna do intervalo D6 a F11.

   

3. Deixe a caixa de diálogo aberta para a próxima etapa do tutorial.

Criar a Fórmula de Matriz

Antes de fechar a caixa de diálogo, transforme a função aninhada em uma fórmula de matriz. Essa matriz permite que a função pesquise vários termos na tabela de dados. Neste tutorial, dois termos são correspondidos: Widgets da coluna 1 e Titanium da coluna 2.

Para criar uma fórmula de matriz no Excel, pressione as teclas CTRL, SHIFT e ENTER simultaneamente. Uma vez pressionada, a função é cercada por chaves, indicando que a função agora é um array.

1. Selecione OK para fechar a caixa de diálogo. No Excel para Mac, selecione Concluído.
2. Selecione a célula F3 para visualizar a fórmula e coloque o cursor no final da fórmula na barra de fórmulas.
3. Para converter a fórmula em uma matriz, pressione CTRL + SHIFT + ENTER.
4. Um erro #N/A aparece na célula F3. Esta é a célula onde a função foi inserida.
5. O erro #N/A aparece na célula F3 porque as células D3 e E3 estão em branco. D3 e E3 são as células onde a função procura para encontrar o Lookup_value. Depois que os dados são adicionados a essas duas células, o erro é substituído por informações do banco de dados.

   

Adicione os critérios de pesquisa

A última etapa é adicionar os termos de pesquisa à planilha. Esta etapa corresponde aos termos Widgets da coluna 1 e Titanium da coluna 2.

Se a fórmula encontrar uma correspondência para ambos os termos nas colunas apropriadas do banco de dados, ela retornará o valor da terceira coluna.

1. Selecione a célula D3.
2. Digite Widgets.
3. Selecione a célula E3.
4. Digite Titanium e pressione Enter.
5. O nome do fornecedor, Widgets Inc., aparece na célula F3. Este é o único fornecedor listado que vende Titanium Widgets.
6. Selecione a célula F3. A função aparece na barra de fórmulas acima da planilha. Neste exemplo, há apenas um fornecedor de widgets de titânio. Caso tenha havido mais de um fornecedor, o fornecedor listado primeiro no banco de dados é retornado pela função.
   
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}