Neste artigo, aprenderemos como Pesquisar n-ésima correspondência na tabela usando a função INDEX & MATCH.
Cenário:
Por exemplo, precisamos encontrar a enésima correspondência numericamente e seus resultados correspondentes na tabela. Precisamos de alguma fórmula geral que ajude a encontrar a enésima correspondência para obter o valor necessário.
Como resolver o problema?
Para que a fórmula seja entendida primeiro precisamos revisar um pouco sobre as seguintes funções
- Função INDEX
- Função MATCH
Agora faremos uma fórmula usando as funções acima. A função MATCH retornará o índice da correspondência mais baixa do intervalo. A função INDEX leva o índice da linha como argumento e retorna seus resultados necessários correspondentes. Esta função encontra o
Fórmula Genérica:
= INDEX (dados, CORRESPONDÊNCIA (PEQUENO (intervalo, n), intervalo, match_type), col_num)
dados : array de valores na tabela sem cabeçalhos
faixa : lookup_array para a correspondência mais baixa
n : número, enésima correspondência
tipo de partida : 1 (exata ou próxima menor) ou 0 (correspondência exata) ou -1 (exata ou a próxima maior)
col_num: número da coluna, valor necessário para recuperar da coluna da tabela.
Exemplo:
As afirmações acima podem ser confusas para entender. Então, vamos entender isso usando a fórmula em um exemplo
Aqui temos uma tabela com detalhes sobre o continente mundial. Precisamos encontrar o código do país na tabela fornecida a partir do valor de pesquisa como nome do país.
Intervalo nomeado usado para tabela (E5: I10) e matriz de intervalo (I5: I10).
Aqui, usamos o intervalo nomeado em vez da matriz de referência de célula, pois é fácil de entender. Precisamos procurar todos os detalhes onde o valor do preço é mínimo ou mais baixo na faixa.
Use a fórmula acima para obter o primeiro detalhe que é maiúsculo
Use a fórmula na célula G6:
= INDEX (tabela, MATCH ( PEQUENA (intervalo, 1) ,intervalo, 0 ) , 4 )
Intervalos nomeados
tabela (E5: I10)
intervalo (I5: I10)
Explicação:
- A função SMALL encontra a primeira correspondência mais baixa no intervalo e retorna o valor para a função MATCH.
- A função MATCH corresponde ao valor mínimo exato na faixa de preço e retorna seu índice de linha para a função INDEX.
- A função INDEX encontra o valor Capital tendo o índice ROW e a 4ª coluna na tabela nomeada intervalo.
A fórmula retorna todos os resultados para o primeiro valor mais baixo. Agora copie a fórmula para obter o segundo menor ou o terceiro menor, basta alterar o argumento n na função PEQUENO para obter os resultados diferentes.
Agora apenas altere o valor n e obtenha os resultados conforme mostrado abaixo.
Como você pode ver no instantâneo acima, obtivemos todos os detalhes correspondentes ao enésimo valor na tabela. Extraia os detalhes da tabela usando a fórmula indicada acima.
Exemplo:
Você também pode usar a função LARGE para obter a enésima correspondência do topo ou a enésima correspondência mais alta do intervalo e retornar seus resultados correspondentes.
Fórmula Genérica:
= INDEX (dados, MATCH (LARGE (intervalo, n), intervalo, match_type), col_num)
dados : array de valores na tabela sem cabeçalhos
faixa : lookup_array para a correspondência mais alta
n : número, enésima correspondência
tipo de partida : 1 (exata ou próxima menor) ou 0 (correspondência exata) ou -1 (exata ou a próxima maior)
col_num: número da coluna, valor necessário para recuperar da coluna da tabela.
Exemplo:
As afirmações acima podem ser confusas para entender. Então, vamos entender isso usando a fórmula em um exemplo
Aqui temos uma tabela com detalhes sobre o continente mundial. Precisamos encontrar o código do país na tabela fornecida a partir do valor de pesquisa como nome do país.
Intervalo nomeado usado para tabela (E5: I10) e matriz de intervalo (I5: I10).
Aqui, usamos o intervalo nomeado em vez da matriz de referência de célula, pois é fácil de entender. Precisamos procurar todos os detalhes onde o valor do preço é máximo ou mais alto no intervalo.
Use a fórmula acima para obter o primeiro detalhe que é maiúsculo
Use a fórmula na célula G6:
= INDEX (tabela, MATCH ( AMPLA (intervalo, 1) ,intervalo, 0 ) , 4 )
Intervalos nomeados
tabela (E5: I10)
intervalo (I5: I10)
Explicação:
- A função LARGE encontra a primeira correspondência mais alta no intervalo e retorna o valor para a função MATCH.
- A função MATCH corresponde à correspondência exata do valor enésimo no intervalo de preços e retorna seu índice de linha para a função INDEX.
- A função INDEX encontra o valor Capital tendo o índice ROW e a 4ª coluna na tabela nomeada intervalo.
A fórmula retorna todos os resultados para o primeiro valor mais baixo. Agora copie a fórmula para obter o segundo menor ou o terceiro menor, basta alterar o argumento n na função PEQUENO para obter os resultados diferentes.
Agora apenas altere o valor n e obtenha os resultados conforme mostrado abaixo.
Como você pode ver no instantâneo acima, obtivemos todos os detalhes correspondentes ao enésimo valor na tabela. Extraia os detalhes da tabela usando a fórmula indicada acima.
Aqui estão algumas notas observacionais ao usar a fórmula acima.
Notas:
- O valor de n não pode ser menor que 1 ou maior que o comprimento do intervalo
- A função retorna o erro #NA se o argumento da matriz de pesquisa para a função MATCH não tiver o mesmo comprimento da matriz da tabela.
- A fórmula retorna um erro se lookup_value não corresponder ao valor na tabela lookup_array.
- A função corresponde ao valor exato, pois o argumento do tipo de correspondência para a função MATCH é 0.
- Os valores de pesquisa podem ser fornecidos como referência de célula ou diretamente usando o símbolo de aspas (") na fórmula como argumentos.
Espero que você tenha entendido como Pesquisar n-ésima correspondência na tabela usando a função INDEX & MATCH. Explore mais artigos sobre valor de pesquisa do Excel aqui. Sinta-se à vontade para expressar suas dúvidas abaixo na caixa de comentários. Certamente iremos ajudá-lo.
Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos no site de e-mail
Use INDEX e MATCH para pesquisar o valor : Função INDEX & MATCH para pesquisar o valor conforme necessário.
Intervalo SUM com INDEX no Excel : Use a função INDEX para encontrar a SOMA dos valores conforme necessário.
Como usar a função INDEX no Excel : Encontre o INDEX da matriz usando a função INDEX explicada com o exemplo.
Como usar a função COMBINAR no Excel : Encontre o MATCH na matriz usando o valor INDEX dentro da função MATCH explicada com o exemplo.
Como usar a função LOOKUP no Excel : Encontre o valor de pesquisa na matriz usando a função LOOKUP explicada com o exemplo.
Como usar a função PROCV no Excel : Encontre o valor de pesquisa na matriz usando a função VLOOKUP explicada com o exemplo.
Artigos populares
50 Atalho do Excel para aumentar sua produtividade
Editar uma lista suspensa
Referência absoluta no Excel
Se com formatação condicional
Se com curingas
Vlookup por data
Junte-se ao nome e sobrenome no excel