O que saber
- Primeiro, crie uma função INDEX, depois 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 da coluna.
- 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.
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.
- Digite o segundo intervalo nas células D5 a F11.
Criar uma função INDEX no Excel
A função ÍNDICE é 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 localização dos dados na tabela.
Neste tutorial, o Array Form é usado para localizar o nome do fornecedor para widgets de titânio, em vez da referência da célula a esse fornecedor no banco de dados.
Siga estes passos para criar a função INDEX:
- Selecione a célula F3 para torná-la a célula ativa. Esta célula é onde a função aninhada será inserida.
-
Vá para Fórmulas.
Image - Escolha Pesquisa e Referência para abrir a lista suspensa de funções.
- Selecione INDEX para abrir a caixa de diálogo Selecionar Argumentos.
- Escolha array, row_num, column_num.
- Selecione OK para abrir a caixa de diálogo Function Arguments. No Excel para Mac, o Formula Builder é aberto.
- Coloque o cursor na caixa de texto Array.
-
Destaque as células D6 até 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.
Image
Iniciar 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.
O primeiro passo 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 juntando duas ou mais referências de célula usando o símbolo e comercial (&).
-
Na caixa de diálogo Function Arguments, coloque o cursor na caixa de texto Row_num.
- Digite MATCH(.
- Selecione a célula D3 para inserir essa referência de célula na caixa de diálogo.
- Digite & (o e comercial) após a referência de célula D3 para adicionar uma segunda referência de célula.
- Selecione a célula E3 para inserir a segunda referência de célula.
-
Enter , (uma vírgula) após a referência de célula E3 para completar a entrada do argumento Lookup_value da função MATCH.
Image 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 abrange 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 MATCH pesquisa apenas uma matriz para cada termo especificado. Para inserir vários arrays, use o e comercial para concatenar os arrays.
- 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.
- Destaque as células D6 até D11 na planilha para inserir o intervalo. Este intervalo é o primeiro array que a função procura.
- Digite & (um e comercial) após a célula referenciar D6:D11. Este símbolo faz com que a função pesquise dois arrays.
- Destaque as células E6 até E11 na planilha para inserir o intervalo. Este intervalo é o segundo array que a função procura.
-
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.
Image - 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 da função MATCH é o argumento Match_type. Esse argumento informa ao Excel como combinar 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 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 ao 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 estes passos após a vírgula inserida no passo anterior na linha Row_num na função INDEX:
- 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.
-
Digite ) (um colchete de fechamento) para completar a função MATCH.
Image - Deixe a caixa de diálogo aberta para a próxima etapa do tutorial.
Termine a função INDEX
A função MATCH está concluída. É hora de ir para a caixa de texto núm_coluna da caixa de diálogo e inserir o último argumento para a função ÍNDICE. 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.
- Coloque o cursor na caixa de texto Column_num.
-
Digite 3 (o número três). Este número diz à fórmula para procurar dados na terceira coluna do intervalo D6 a F11.
Image - Deixe a caixa de diálogo aberta para a próxima etapa do tutorial.
Criar a Fórmula do Array
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 Titânio da coluna 2.
Para criar uma fórmula de matriz no Excel, pressione CTRL, SHIFT e ENTERteclas simultaneamente. Uma vez pressionada, a função é cercada por chaves, indicando que a função agora é um array.
- Selecione OK para fechar a caixa de diálogo. No Excel para Mac, selecione Concluído.
- Selecione a célula F3 para visualizar a fórmula e coloque o cursor no final da fórmula na Barra de Fórmulas.
- Para converter a fórmula em uma matriz, pressione CTRL+ SHIFT+ ENTER.
- A N/A erro aparece na célula F3. Esta é a célula onde a função foi inserida.
-
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 encontrar o Lookup_value. Depois que os dados são adicionados a essas duas células, o erro é substituído pelas informações do banco de dados.
Image
Adicione os critérios de pesquisa
O último passo é 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.
- Selecionar célula D3.
- Digite Widgets.
- Selecione a célula E3.
- Digite Titanium e pressione Enter.
- O nome do fornecedor, Widgets Inc., aparece na célula F3. Este é o único fornecedor listado que vende widgets de titânio.
-
Selecione a célula F3. A função aparece na barra de fórmulas acima da planilha.
{=INDEX(D6:F11, CORRESP(D3&E3, D6:D11&E6:E11, 0), 3)}
Neste exemplo, há apenas um fornecedor de widgets de titânio. Se houver mais de um fornecedor, o fornecedor listado primeiro no banco de dados é retornado pela função.
Image
