O que saber
- A função INDEX pode ser usada sozinha, mas aninhar a função MATCH dentro dela cria uma pesquisa avançada.
- Essa função aninhada é mais flexível do que VLOOKUP e pode produzir 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.
O que 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. 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 as duas 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, [match_type])
- 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 for omitido.
| Qual tipo de correspondência usar | |||
|---|---|---|---|
| Tipo de partida | O que faz | Regra | Exemplo |
| 1 | Encontra o maior valor que é 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 AZ; 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. |
Usar 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 são aceitáveis. Mas lembre-se que se você não especificar tipo de partida, 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 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) procura em 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 de 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 assim, uma vez que 2 é o que MATCH encontrou: ÍNDICE (B2: B5, 2, [column_num]).
- 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 (H1, A2: A13,0), CORRESPONDÊNCIA (H2, B1: E1,0))
Neste exemplo de MATCH e INDEX, estamos fazendo uma pesquisa bidirecional. A ideia é ver quanto dinheiro ganhamos com Verde itens em Maio. Isso é realmente semelhante ao exemplo acima, mas uma fórmula MATCH extra está aninhada em INDEX.
- CORRESPONDÊNCIA (H1, A2: A13,0) é o primeiro item resolvido nesta fórmula. Está procurando por H1 (a palavra “maio”) em A2: A13 para obter um determinado valor. Não vemos isso aqui, mas é 5.
- CORRESPONDÊNCIA (H2, B1: E1,0) é a segunda fórmula MATCH, e é muito semelhante à primeira, mas em vez disso está procurando H2 (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 faz distinção entre 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 único 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. Mais da Lifewire
-
Como criar uma fórmula de pesquisa do Excel com vários critérios
-
Como usar a função IF no Excel
-
Como usar a função INDEX do Excel
-
Como criar um banco de dados no Excel
-
Como usar a função MATCH do Excel para encontrar um valor
-
Como usar a função LOOKUP do Excel para encontrar informações
-
Como usar a função LOOKUP no Excel
-
Como usar VLOOKUP no Planilhas Google
-
Como usar ARRAYFORMULA no Planilhas Google
-
Como combinar as funções MIN e IF em uma fórmula de matriz do Excel
-
Como combinar as funções ROUND e SUM no Excel
-
Como usar a função PROCV no Excel
-
Encontre o maior número negativo ou positivo no Excel
-
Encontre dados com as funções ROW e COLUMN do Excel
-
Como usar a referência de célula absoluta no Excel
-
Como usar valores booleanos (valores lógicos) no Excel
Seus direitos de privacidade
A Lifewire e nossos parceiros terceirizados usam cookies e processam dados pessoais como identificadores exclusivos com base em seu consentimento para armazenar e / ou acessar informações em um dispositivo, exibir anúncios personalizados e para medição de conteúdo, percepção do público e desenvolvimento de produtos. Para alterar ou retirar suas opções de consentimento para Lifewire.com, incluindo seu direito de contestar quando um interesse legítimo é usado, clique abaixo. A qualquer momento, você pode atualizar suas configurações por meio do link “Privacidade da UE” na parte inferior de qualquer página. Essas escolhas serão sinalizadas globalmente para nossos parceiros e não afetarão os dados de navegação. Lista de parceiros (fornecedores)
Nós e nossos parceiros processamos dados para:
Verifique ativamente as características do dispositivo para identificação. Use dados precisos de geolocalização. Armazene e / ou acesse informações em um dispositivo. Selecione o conteúdo personalizado. Crie um perfil de conteúdo personalizado. Avalie o desempenho do anúncio. Selecione anúncios básicos. Crie um perfil de anúncios personalizados. Selecione anúncios personalizados. Aplique pesquisas de mercado para gerar insights do público. Avalie o desempenho do conteúdo. Desenvolva e melhore produtos. Lista de parceiros (fornecedores)