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 Match_type argumento e, em seguida, especifique o intervalo da coluna.
• Em seguida, transforme a função aninhada em uma fórmula de matriz pressionando Ctrl+Mudança+Entrar. Finalmente, 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 da 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.

Acompanhe 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.

Crie uma função INDEX no Excel

A função INDEX é uma das poucas funções do Excel que possui vários formulários. A função possui um Formulário de Matriz e um Formulário de Referência. O Array Form 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 a localização dos dados na tabela. Neste tutorial, o Array Form é 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. Vamos para Fórmulas.

   

3. Escolher Pesquisa e Referência para abrir a lista suspensa de funções.

4. Selecione ÍNDICE para abrir o Selecione os argumentos caixa de diálogo.

5. Escolher matriz, núm_linha, núm_coluna.

6. Selecione OK para abrir o Argumentos de função caixa de diálogo. No Excel para Mac, o Formula Builder é aberto.

7. Coloque o cursor no Variedade caixa de texto.

8. Destacar células D6 Através dos 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.

   

Iniciar a função MATCH aninhada

Ao aninhar uma função dentro de outra, não é possível abrir o segundo, ou o construtor de fórmula da função 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 valor_procurado é o local ou referência de célula para o termo de pesquisa a ser correspondido no banco de dados. O valor_procurado aceita apenas um critério ou termo de pesquisa. Para pesquisar vários critérios, estenda Lookup_value concatenando ou unindo duas ou mais referências de célula usando o símbolo “e” comercial (&).

1. No Argumentos de função caixa de diálogo, coloque o cursor no Row_num caixa de texto.

2. Entrar PARTIDA(.

3. Selecione a célula D3 para inserir essa referência de célula na caixa de diálogo.

4. Entrar E (o e comercial) após a referência da 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. Entrar , (uma vírgula) após a referência de célula E3 para completar a entrada do argumento Valor_procurado da função CORRESP.

   

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

Complete a função MATCH aninhada

Esta etapa cobre 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 localizar 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 Lookup_array. A função MATCH pesquisa apenas um array 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 no Row_num caixa de texto. O cursor aparece após a vírgula no final da entrada atual.

2. Destacar células D6 Através dos D11 na planilha para inserir o intervalo. Este intervalo é a primeira matriz pesquisada pela função.

3. Entrar E (um E comercial) após as referências de célula D6: D11. Este símbolo faz com que a função pesquise duas matrizes.

4. Destacar células E6 Através dos E11 na planilha para inserir o intervalo. Este intervalo é a segunda matriz pesquisada pela função.

5. Entrar , (uma vírgula) após a referência da 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 do tipo MATCH

O terceiro e último argumento do A função MATCH é Match_type argumento. Este argumento informa ao Excel como combinar o valor_procurado com os valores do array_procurado. As opções disponíveis são 1, 0 ou -1. Este argumento é opcional. Se for omitido, a função usará o valor padrão de 1.

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

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

1. Entrar 0 (um zero) após a vírgula no Row_num caixa de texto. Esse número faz com que a função aninhada retorne correspondências exatas para os termos inseridos nas células D3 e E3.

2. Entrar ) (um colchete de fechamento) para completar a função MATCH.

   

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

Conclua a função INDEX

A função MATCH 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 de 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 no Column_num caixa de texto.

2. Entrar 3 (o número três). Este número informa a 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.

Crie 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 combinados: Widgets da coluna 1 e Titanium da coluna 2. Para criar uma fórmula de matriz no Excel, pressione o botão CTRL, MUDANÇA, e ENTRAR teclas simultaneamente. Uma vez pressionada, a função é cercada por chaves, indicando que a função agora é uma matriz.

1. Selecione OK para fechar a caixa de diálogo. No Excel para Mac, selecione Feito.

2. Selecione a célula F3 para visualizar a fórmula, coloque o cursor no final da fórmula na Barra de Fórmulas.

3. Para converter a fórmula em uma matriz, pressione CTRL+MUDANÇA+ENTRAR.

4. A # N / A o erro 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 valor_procurado. 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 no banco de dados, ela retornará o valor da terceira coluna.

1. Selecione a célula D3.

2. Entrar Widgets.

3. Selecione a célula E3.

4. Modelo Titânioe pressione Entrar.

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. {= ÍNDICE (D6: F11, CORRESPONDÊNCIA (D3 e E3, D6: D11 e E6: E11,0), 3)}

   Neste exemplo, há apenas um fornecedor para widgets de titânio. Se houvesse mais de um fornecedor, o fornecedor listado primeiro no banco de dados é retornado pela função.