Programas de planilhas como o Microsoft Excel e o Google Sheets oferecem várias funções para pesquisar valores nas suas tabelas. Os dois oferecem HLOOKUP e VLOOKUP para pesquisas horizontais e verticais, além de XLOOKUP, que combina as duas e oferece mais flexibilidade. No entanto, usá-las com vários critérios pode ser bem complicado, porque exige vários níveis de aninhamento. Felizmente, combinar as funções INDEX (ÍNDICE) e MATCH (CORRESP) permite fazer pesquisas flexíveis e complexas com vários critérios.
Neste guia, primeiro você vai revisar as funções INDEX e MATCH e como combiná-las para fazer pesquisas. Depois, vai ver como um pequeno ajuste permite usar vários critérios nas suas pesquisas, sejam verticais, horizontais ou mistas. Você tem instruções passo a passo para pesquisar valores com vários critérios em uma pesquisa horizontal, em uma vertical e em uma com critérios horizontais e verticais. Por fim, vai aprender a combinar INDEX e MATCH com outras funções, como AVERAGE (MÉDIA).
Sintaxe das funções INDEX e MATCH
Antes de começar com as pesquisas baseadas em vários critérios, vale a pena revisar a sintaxe das duas funções e também a sintaxe combinada.
=INDEX(reference, row*, column*)
- reference: o intervalo de células do qual você quer obter os valores.
- row*: opcional. O índice da linha a ser retornada dentro do intervalo de células de referência. 0 por padrão. É aqui que você adiciona MATCH para as pesquisas verticais.
- column*: opcional. O índice da coluna a ser retornada dentro do intervalo de células de referência. 0 por padrão. É aqui que você adiciona MATCH para as pesquisas horizontais.
=MATCH(search_key, range, search_type*)
- search_key: o valor que você quer pesquisar.
- range: a matriz unidimensional na qual pesquisar.
- search_type*: opcional. A forma de pesquisar.
- 1 é o valor padrão. A função considera que o intervalo está em ordem crescente e retorna o maior valor menor ou igual à chave de pesquisa.
- 0 encontra uma correspondência exata. O intervalo não precisa estar classificado.
- -1 considera que o intervalo está em ordem decrescente e retorna o menor valor maior ou igual à chave de pesquisa.
Sintaxe combinada de INDEX e MATCH
Para usar a função INDEX, você precisa indicar a linha e a coluna do seu intervalo. A função então retorna o conteúdo da célula onde essa linha e essa coluna se cruzam. Em outras palavras, você precisa informar as coordenadas ou referências de célula.
A função MATCH, por outro lado, pesquisa um valor específico e retorna as coordenadas ou a referência de célula. Na verdade, é exatamente o que você precisa para tornar a função INDEX dinâmica. Como indicado acima, você pode substituir os argumentos de linha e coluna do INDEX por funções MATCH.
=INDEX(range,MATCH(search_key,search_column,search_type),MATCH(search_key,search_row,search_type))
Com a fórmula acima, você pode indicar o critério de linha e o de coluna da sua pesquisa, mas fica limitado a um de cada. Felizmente, dá para mudar isso com um pequeno ajuste na fórmula.
Sintaxe combinada para pesquisas com vários critérios
Dentro da função MATCH, você pode substituir search_key por "1" e search_column ou search_row por um conjunto de critérios ou condições. Cada condição deve ficar entre parênteses e ser unida às outras por um sinal de multiplicação. Assim, a função avalia cada condição e só retorna "1" se todas forem atendidas.
Pesquisa vertical com vários critérios
Use a fórmula a seguir para uma pesquisa vertical com vários critérios.
=INDEX(reference,MATCH(1,(criteria1)*(criteria2)*(criteriaN),0))
Pesquisa horizontal com vários critérios
Use a fórmula a seguir para uma pesquisa horizontal com vários critérios.
=INDEX(reference,row,MATCH(1,(criteria1)*(criteria2)*(criteriaN),0))
Pesquisa vertical e horizontal com vários critérios
Use a fórmula a seguir para uma pesquisa com vários critérios verticais e horizontais.
=INDEX(reference,MATCH(1,(criteria1)*(criteria2)*(criteriaN),0),MATCH(1,(criteria1)*(criteria2)*(criteriaN),0))
Como usar INDEX e MATCH com vários critérios no Google Sheets
Combinar as funções INDEX e MATCH permite fazer pesquisas que encontram valores com vários critérios. Em outras palavras, permite eliminar a ambiguidade quando há várias correspondências possíveis para um mesmo valor. Essas pesquisas podem ser verticais, horizontais ou as duas coisas.
Felizmente, as duas funções estão disponíveis no Microsoft Excel e no Google Sheets. Abaixo, você tem instruções para fazer uma pesquisa com dois critérios: um vertical e um horizontal. Na próxima seção, há exemplos com vários critérios verticais ou horizontais. Por fim, há um exemplo que combina INDEX, MATCH e AVERAGE.
1. Configure os critérios
1. Abra no Google Sheets a planilha onde você quer os resultados da pesquisa. Neste caso, quero os resultados na mesma página que os dados, mas você pode usar IMPORTRANGE para pesquisar em outros arquivos.
2. Digite os critérios, ou os valores que você quer pesquisar, em células separadas. Neste caso, o primeiro critério indica a linha e o segundo, a coluna.
Você pode adicionar listas suspensas nas células de critérios para trocá-los com mais facilidade. Para aprender a adicionar listas suspensas no Google Sheets, confira este guia sobre como adicionar uma lista suspensa no Google Sheets.
2. Função INDEX
1. Digite a fórmula INDEX na célula onde você quer o resultado da pesquisa.
2. Como primeiro argumento, selecione o intervalo de células que contém a tabela.
3. Função MATCH: vertical
1. Como segundo argumento do INDEX, digite a função MATCH.
2. Como primeiro argumento, selecione a célula com o valor de pesquisa vertical.
3. Como segundo argumento, selecione a coluna na qual você quer pesquisar.
4. O terceiro argumento deve ser "0", já que você quer uma correspondência exata. Feche o parêntese e adicione uma vírgula.
4. Função MATCH: horizontal
1. Por fim, adicione outra função MATCH. Ela é o terceiro parâmetro da função INDEX, que indica a coluna.
2. Selecione a célula com o valor de pesquisa horizontal como primeiro argumento.
3. Selecione a linha na qual você quer pesquisar como segundo argumento.
4. Por fim, digite "0" como terceiro argumento e adicione dois parênteses de fechamento.
5. Pressione "Enter" para ver o resultado.
6. A função é recalculada automaticamente assim que você muda os critérios.
INDEX e MATCH com vários critérios: exemplos
Nesta seção, você tem dois exemplos. No primeiro, vai encontrar uma correspondência usando vários critérios verticais e um critério horizontal. No segundo, vai combinar INDEX e MATCH com AVERAGE para obter o preço médio de um produto específico de um determinado fornecedor.
Pesquisa com vários critérios verticais e um critério horizontal
1. Abra no Google Sheets a planilha com seus dados.
2. Digite os critérios em células separadas. Adicionei listas suspensas a essas células para facilitar a seleção dos critérios.
3. Na célula onde você quer o resultado, digite a função INDEX e selecione a tabela como primeiro parâmetro.
4. Como segundo parâmetro, adicione a função MATCH. Adicione "1" como chave de pesquisa.
5. Depois da vírgula, abra o parêntese e adicione o critério ou a condição. Feche o parêntese e adicione um sinal de multiplicação.
6. Abra outro parêntese para adicionar o segundo critério. Quando terminar, feche o parêntese e adicione uma vírgula.
7. Não se esqueça de adicionar "0" para uma correspondência exata, fechar o parêntese e adicionar outra vírgula.
8. Adicione outra função MATCH. Como você tem só um critério horizontal, basta selecionar a chave de pesquisa (o mês) e a linha onde ela deve ser encontrada.
9. Por fim, digite "0" para uma correspondência exata e feche os dois parênteses.
10. Pressione "Enter" para ver o resultado.
Pesquisa com vários critérios e AVERAGE
Neste exemplo, você vai aprender a obter o preço médio de um produto e um fornecedor específicos ao longo dos seis meses de dados que você tem.
1. Na planilha onde você quer o resultado, digite os critérios em células separadas.
2. Na célula onde você quer o resultado, digite a função AVERAGE.
3. Em seguida, digite a função INDEX.
4. Selecione a tabela como primeiro parâmetro da função INDEX.
5. Adicione a função MATCH e digite "1" como chave de pesquisa.
6. Depois da vírgula, adicione cada um dos seus critérios verticais entre parênteses, unidos por um sinal de multiplicação.
7. Depois da vírgula, adicione "0" para uma correspondência exata e feche os três parênteses.
8. Pressione "Enter" para ver o resultado.
Conclusão
INDEX e MATCH oferecem uma forma mais flexível de fazer pesquisas no Microsoft Excel e no Google Sheets. Você pode pesquisar valores rapidamente em tabelas complexas usando vários critérios verticais e horizontais. Agora você sabe usar INDEX e MATCH para obter valores com base em critérios verticais e horizontais, incluindo vários de cada tipo. Também sabe combinar as funções INDEX e MATCH com outras para incorporar o cálculo diretamente na sua fórmula de pesquisa. Se você precisa somar os valores de todas as linhas que atendem às suas condições, em vez de retornar uma única correspondência, use SUMIFS com vários critérios.
Para saber mais sobre outras funções de pesquisa, confira estes guias:



