Como usar as funções INDEX e MATCH no Excel

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.

O que são as funções INDEX e MATCH?

INDEX e MATCH são pesquisa do Excel funções. Embora sejam duas funções totalmente separadas que podem ser usadas sozinhas, 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 particular. 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.

Embora INDEX possa muito bem ser usado sozinho, aninhar MATCH na fórmula o torna um pouco mais útil. A função MATCH procura um item especificado em um intervalo de células e, a seguir, 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.

Sintaxe e argumentos de INDEX e MATCH

É assim que ambas as funções precisam ser escritas para que o Excel as compreenda:

= INDEX(variedade, row_num, [column_num])

• variedade é o intervalo de células que a fórmula usará. Pode ser uma ou mais linhas e colunas, como A1: D5. É requerido.
• row_num é a linha na matriz da qual retornar um valor, como 2 ou 18. É obrigatório, a menos que column_num é presente.
• column_num é a coluna na matriz da qual retornar um valor, como 1 ou 9. É opcional.

= JOGO(lookup_value, lookup_array, [tipo de partida])

• lookup_value é o valor que você deseja combinar 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 a examinar. Pode ser uma única linha ou uma única coluna, como A2: D2 ou G1: G45. Isso é obrigatório.
• tipo de partida pode ser -1, 0, ou 1. Especifica como lookup_value é correspondido com valores em lookup_array (Veja abaixo). 1 é o valor padrão se este argumento é omitido.

Qual tipo de correspondência usar
Tipo de partida	O que faz	Regra	Exemplo
1	Encontra o maior valor menor ou igual a lookup_value.	o lookup_array os valores devem ser colocados em ordem crescente (por exemplo, -2, -1, 0, 1, 2; ou A-Z; ou FALSE, TRUE.	lookup_value é 25, mas está faltando 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.	o lookup_array os valores podem estar em qualquer ordem.	lookup_value é 25, então ele retorna a posição de 25.
-1	Encontra o menor valor maior ou igual a lookup_value.	o lookup_array os valores devem ser colocados em ordem decrescente (por exemplo, 2, 1, 0, -1, -2).	lookup_value é 25, mas está faltando em lookup_array, então a posição do próximo maior número, como 34, é retornada.

Exemplo de fórmulas INDEX e MATCH

Antes de examinarmos como combinar INDEX e MATCH em uma fórmula, precisamos entender como essas funções funcionam por conta própria.

Exemplos INDEX

= ÍNDICE (A1: B2,2,2)
= ÍNDICE (A1: B1,1)
= INDEX (2: 2,1)
= ÍNDICE (B1: B2,1)

Neste primeiro exemplo, existem quatro fórmulas INDEX que podemos usar para obter valores diferentes:

• = ÍNDICE (A1: B2,2,2) examina A1: B2 para encontrar o valor na segunda coluna e segunda linha, que é Stacy.
• = ÍNDICE (A1: B1,1) analisa 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.
• = ÍNDICE (B1: B2,1) olha em B1: B2 para localizar o valor na primeira linha, que é Amy.

Exemplos de MATCH

= CORRESPONDÊNCIA ("Stacy", A2: D2,0)
= CORRESPONDÊNCIA (14, D1: D2)
= CORRESPONDÊNCIA (14, D1: D2, -1)
= CORRESPONDÊNCIA (13, A1: D1,0)

Aqui estão quatro exemplos fáceis da função MATCH:

• = CORRESPONDÊNCIA ("Stacy", A2: D2,0) está procurando por Stacy no intervalo A2: D2 e retorna 3 como resultado.
• = CORRESPONDÊNCIA (14, D1: D2) está procurando por 14 no intervalo D1: D2, mas como não é encontrado na tabela, MATCH encontra o próximo maior valor que é menor ou igual a 14, que neste caso é 13, que está em posição 1 do lookup_array.
• = CORRESPONDÊNCIA (14, D1: D2, -1) é idêntica à fórmula acima dela, mas como a matriz não está em ordem decrescente, como -1 requer, obtemos um erro.
• = CORRESPONDÊNCIA (13, A1: D1,0) está procurando 13 na primeira linha da folha, que retorna 4 já que é o quarto item nesta matriz.

Exemplos de INDEX-MATCH

Aqui estão dois exemplos onde podemos combinar INDEX e MATCH em uma fórmula:

Encontre referência de célula na tabela

= ÍNDICE (B2: B5, CORRESPONDÊNCIA (F1, A2: A5))

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 sozinhas, mas como estamos aninhando-as, é o que está acontecendo:

• CORRESPONDÊNCIA (F1, A2: A5) está procurando pelo F1 valor (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 já que estamos procurando o valor dessa coluna.
• A função INDEX agora pode ser reescrita desta forma, uma vez que 2 é o que MATCH encontrou: ÍNDICE (B2: B5, 2, [núm_coluna]).
• Desde a column_num é opcional, podemos removê-lo para ficar com o seguinte: ÍNDICE (B2: B5,2).
• Então, agora, isso é como uma fórmula INDEX normal, onde encontramos o valor do segundo item em B2: B5, qual é vermelho.

Pesquisa por títulos de linha e coluna

= ÍNDICE (B2: E13, CORRESPONDÊNCIA (G1, A2: A13,0), CORRESPONDÊNCIA (G2, B1: E1,0))

Neste exemplo de MATCH e INDEX, estamos fazendo uma pesquisa bidirecional. A ideia é ver quanto dinheiro ganhamos com Verde itens em Poderia. Isso é realmente semelhante ao exemplo acima, mas uma fórmula MATCH extra está aninhada em INDEX.

• CORRESPONDÊNCIA (G1, A2: A13,0) é o primeiro item resolvido nesta fórmula. Está procurando por G1 (a palavra "maio") em A2: A13 para obter um determinado valor. Não vemos isso aqui, mas é 5.
• PARTIDA (G2, B1: E1,0) é a segunda fórmula MATCH, e é muito semelhante à primeira, mas em vez disso está procurando G2 (a palavra "Verde") 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: = ÍNDICE (B2: E13,5,3). Isso está olhando em toda a tabela, B2: E13, para a quinta linha e terceira coluna, que retorna $180.

Regras MATCH e INDEX

Há várias coisas a se ter em mente ao escrever fórmulas com essas funções:

• MATCH não é maiúsculas e Minúsculas, portanto, letras maiúsculas e minúsculas são tratadas da mesma forma ao corresponder aos valores de texto.
• MATCH retorna #N / D por vários motivos: se tipo de partida é 0 e lookup_value não é encontrado se tipo de partida é -1 e lookup_array não está em ordem decrescente, se tipo de partida é 1 e lookup_array não está em ordem crescente, e se lookup_array não é uma única linha ou coluna.
• Você pode usar um caractere curinga no lookup_value argumento se tipo de partida é 0 e lookup_value é uma string de texto. Um ponto de interrogação corresponde a qualquer caractere e um asterisco corresponde a qualquer sequência de caracteres (por exemplo, = CORRESPONDÊNCIA ("Jo *", 1: 1,0)). Para usar o MATCH para encontrar um ponto de interrogação ou asterisco real, digite ~ primeiro.
• INDEX retorna #REF! E se row_num e column_num não aponte para uma célula dentro da matriz.

Funções relacionadas do Excel

A função MATCH é semelhante a LOOKUP, mas MATCH retorna o 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 de VLOOKUP só precisam dessa função.