A função INDEX pode ser usada sozinha, mas aninhar a função MATCH dentro dela cria uma pesquisa avançada.
Esta função aninhada é mais flexível que VLOOKUP e pode gerar resultados mais rapidamente.
Este artigo explica como usar as funções INDEX e MATCH juntas em todas as versões do Excel, incluindo Excel 2019 e Microsoft 365.
Quais são as funções INDEX e MATCH?
INDEX e MATCH são funções de pesquisa do Excel. Embora sejam duas funções totalmente separadas que podem ser usadas sozinhas, elas também podem ser combinadas para criar fórmulas avançadas.
A função INDEX retorna um valor ou a referência a um valor de uma seleção específica. Por exemplo, pode ser usado para encontrar o valor na segunda linha de um conjunto de dados ou na quinta linha e na terceira coluna.
Enquanto INDEX poderia muito bem ser usado sozinho, aninhar MATCH na fórmula o torna um pouco mais útil. A função CORRESP procura um item especificado em um intervalo de células e, em seguida, retorna a posição relativa do item no intervalo. Por exemplo, pode ser usado para determinar que um nome específico é o terceiro item em uma lista de nomes.
Image
INDEX e MATCH Sintaxe e Argumentos
É assim que ambas as funções precisam ser escritas para que o Excel as entenda:
=INDEX(array, row_num, [column_num])
array é o intervalo de células que a fórmula usará. Pode ser uma ou mais linhas e colunas, como A1:D5. É obrigatório.
row_num é a linha na matriz da qual retornar um valor, como 2 ou 18. É obrigatório, a menos que núm_coluna esteja presente.
column_num é a coluna na matriz da qual retornar um valor, como 1 ou 9. É opcional.
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value é o valor que você deseja combinar em lookup_array. Pode ser um número, texto ou valor lógico digitado manualmente ou referido por meio de uma referência de célula. Isso é obrigatório.
lookup_array é o intervalo de células para examinar. Pode ser uma única linha ou uma única coluna, como A2:D2 ou G1:G45. Isso é obrigatório.
match_type pode ser -1, 0 ou 1. Ele especifica como lookup_value é combinado com valores em lookup_array (veja abaixo). 1 é o valor padrão se este argumento for omitido.
Qual tipo de correspondência usar
Tipo de correspondência
O que faz
Regra
Exemplo
1
Encontra o maior valor que é menor ou igual a lookup_value.
Os valores lookup_array devem ser colocados em ordem crescente (por exemplo, -2, -1, 0, 1, 2; ou A-Z; ou FALSE, TRUE.
lookup_value é 25, mas está f altando em lookup_array, então a posição do próximo menor número, como 22, é retornada.
0
Encontra o primeiro valor que é exatamente igual a lookup_value.
Os valores lookup_array podem estar em qualquer ordem.
lookup_value é 25, então ele retorna a posição 25.
-1
Encontra o menor valor que é maior ou igual a lookup_value.
Os valores lookup_array devem ser colocados em ordem decrescente (por exemplo, 2, 1, 0, -1, -2).
lookup_value é 25, mas está f altando em lookup_array, então a posição do próximo maior número, como 34, é retornada.
Use 1 ou -1 para momentos em que você precisa executar uma pesquisa aproximada ao longo de uma escala, como ao lidar com números e quando as aproximações estão corretas. Mas lembre-se que se você não especificar match_type, 1 será o padrão, o que pode distorcer os resultados se você realmente quiser uma correspondência exata.
Exemplo de fórmulas INDEX e MATCH
Antes de vermos como combinar INDEX e MATCH em uma fórmula, precisamos entender como essas funções funcionam sozinhas.
INDEX Exemplos
=ÍNDICE(A1:B2, 2, 2)
=ÍNDICE(A1:B1, 1)
=ÍNDICE(2:2, 1)=ÍNDICE(B1:B2, 1)
Image
Neste primeiro exemplo, existem quatro fórmulas INDEX que podemos usar para obter valores diferentes:
=INDEX(A1:B2, 2, 2) examina A1:B2 para encontrar o valor na segunda coluna e segunda linha, que é Stacy.
=INDEX(A1:B1, 1) examina A1:B1 para encontrar o valor na primeira coluna, que é Jon.
=INDEX(2:2, 1) examina tudo na segunda linha para localizar o valor na primeira coluna, que é Tim.
=INDEX(B1:B2, 1) examina B1:B2 para localizar o valor na primeira linha, que é Amy.
MATCH Exemplos
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)
Image
Aqui estão quatro exemplos fáceis da função MATCH:
=MATCH("Stacy", A2:D2, 0) está procurando por Stacy no intervalo A2:D2 e retorna 3 como resultado.
=MATCH(14, D1:D2) está procurando por 14 no intervalo D1:D2, mas como não foi encontrado na tabela, MATCH encontra o próximo maior valor que é menor ou igual a 14, que neste caso é 13, que está na posição 1 de lookup_array.
=MATCH(14, D1:D2, -1) é idêntico à fórmula acima, mas como a matriz não está em ordem decrescente como -1 requer, obtemos um erro.
=MATCH(13, A1:D1, 0) está procurando por 13 na primeira linha da planilha, que retorna 4, pois é o quarto item neste array.
INDEX-MATCH Exemplos
Aqui estão dois exemplos onde podemos combinar INDEX e MATCH em uma fórmula:
Encontrar referência de célula na tabela
=ÍNDICE(B2:B5, CORRESP(F1, A2:A5))
Image
Este exemplo está aninhando a fórmula MATCH dentro da fórmula INDEX. O objetivo é identificar a cor do item usando o número do item.
Se você olhar para a imagem, poderá ver nas linhas "Separadas" como as fórmulas seriam escritas por conta própria, mas como estamos aninhando-as, é isso que está acontecendo:
MATCH(F1, A2:A5) está procurando o valor F1 (8795) no conjunto de dados A2:A5. Se contarmos a coluna, podemos ver que é 2, então é isso que a função MATCH acabou de descobrir.
A matriz INDEX é B2:B5, pois estamos procurando o valor nessa coluna.
A função INDEX agora pode ser reescrita assim, pois 2 é o que MATCH encontrou: INDEX(B2:B5, 2, [column_num]).
Como column_num é opcional, podemos removê-lo para ficar com isso: INDEX(B2:B5, 2).
Agora, isso é como uma fórmula INDEX normal onde encontramos o valor do segundo item em B2:B5, que é vermelho.
Neste exemplo de MATCH e INDEX, estamos fazendo uma pesquisa bidirecional. A idéia é ver quanto dinheiro ganhamos com os itens verdes em maio. Isso é muito semelhante ao exemplo acima, mas uma fórmula MATCH extra está aninhada em INDEX.
MATCH(G1, A2:A13, 0) é o primeiro item resolvido nesta fórmula. Ele está procurando por G1 (a palavra "maio") em A2:A13 para obter um valor específico. Não vemos aqui, mas são 5.
MATCH(G2, B1:E1, 0) é a segunda fórmula MATCH, e é muito semelhante à primeira, mas está procurando por G2 (a palavra "Green") nos títulos das colunas em B1:E1. Este resolve para 3.
Agora podemos reescrever a fórmula INDEX assim para visualizar o que está acontecendo: =INDEX(B2:E13, 5, 3). Isso está procurando em toda a tabela, B2:E13, a quinta linha e a terceira coluna, que retorna $ 180.
Regras de CORRESP e ÍNDICE
Há várias coisas a serem lembradas ao escrever fórmulas com estas funções:
MATCH não diferencia maiúsculas de minúsculas, portanto, letras maiúsculas e minúsculas são tratadas da mesma forma ao combinar valores de texto.
MATCH retorna N/A por vários motivos: se match_type for 0 e lookup_value não for encontrado se match_type for -1 e lookup_array não estiver em ordem decrescente, se match_type for 1 e lookup_array não estiver em ordem crescente ordem, e se lookup_array não for uma única linha ou coluna.
Você pode usar um caractere curinga no argumento lookup_value se match_type for 0 e lookup_value for uma string de texto. Um ponto de interrogação corresponde a qualquer caractere único e um asterisco corresponde a qualquer sequência de caracteres (por exemplo.ex., =MATCH("Jo", 1:1, 0)). Para usar MATCH para encontrar um ponto de interrogação ou asterisco real, digite ~ primeiro.
INDEX retorna REF! se núm_linha e núm_coluna não apontarem para uma célula dentro da matriz.
Funções Relacionadas do Excel
A função MATCH é semelhante a LOOKUP, mas MATCH retorna a posição do item em vez do próprio item.
VLOOKUP é outra função de pesquisa que você pode usar no Excel, mas ao contrário de MATCH que requer INDEX para pesquisas avançadas, as fórmulas PROCV só precisam dessa função.
A função ISBLANK do Microsoft Excel pode ser uma ótima ferramenta para encontrar falhas em seu banco de dados. Veja como usá-lo ao máximo, com formatação condicional e não
O Microsoft Excel fornece funções não apenas para números, mas também para texto. A função MID do Excel ajudará você a obter texto do meio do conteúdo das células. Aqui está como usá-lo
Use a função CORRESP do Excel para encontrar a primeira posição relativa de valores específicos em uma lista ou tabela de dados. Atualizado para incluir o Excel 2019
