Com VLOOKUP, sempre obtemos a primeira correspondência. O mesmo acontece com a função INDEX MATCH. Então, como VLOOKUP corresponde à segunda, terceira ou enésima? Neste artigo, aprenderemos como obter a enésima ocorrência de um valor no intervalo.
Fórmula Genérica
{= PEQUENO (SE (intervalo = valor,FILEIRA(faixa)-FILEIRA(first_cell_in_range)+1),n)}
Nota: este é um fórmula de matriz. Você precisa inseri-lo com CTRL + SHIFT + ENTER.
Faixa: o intervalo no qual você deseja pesquisar nª posição de valor.
Valor: o valor que você está procurando nª posição nofaixa.
First_cell_in_range: a primeira célula dofaixa. Se o intervalo for A2: A10, a primeira célula do intervalo será A2.
n: a ocorrência número de valores.
Vamos ver um exemplo para deixar as coisas claras.
Exemplo: Encontre a segunda correspondência no Excel
Então, aqui eu tenho esta lista de nomes no intervalo A2 do Excel: A10. Eu chamei este intervalo de nomes. Agora eu quero obter a posição da segunda ocorrência de “Rony” em nomes.
Na imagem acima, podemos ver que está na 7ª posição no intervalo A2: A10 (nomes). Agora precisamos obter sua posição usando uma fórmula do Excel.
Aplique a fórmula genérica acima em C2 para pesquisar a segunda ocorrência de Rony na lista.
{= PEQUENO (SE (nomes = “Rony” ,FILEIRA(nomes)-FILEIRA(A2)+1),2)}
Insira-o com CTRL + SHIFT + ENTER …
E nós temos uma resposta. Ele está mostrando 7, o que é correto. Se você alterar o valor de n para 3, resultará em 8. Se você alterar o valor de n maior do que a ocorrência do valor no intervalo, ele retornará o erro #NUM.
Como funciona?
Bem, é muito fácil. Vamos ver cada parte uma por uma.
E SE(nomes = “Rony” ,FILEIRA(nomes)-FILEIRA(A2)+1) :
Em IF, names = “Rony” retorna uma matriz TRUE e FALSE. TRUE sempre que uma célula no intervalo nomes (A2: A10) corresponde a “Rony”. {VERDADEIRO; FALSO; FALSO; FALSO; FALSO; FALSO; VERDADEIRO; VERDADEIRO; FALSO}.
Próxima ROW (nomes)-FILEIRA(A2)+1:
FILEIRA(nomes): aqui a função ROW retorna o número da linha de cada célula nos nomes. {2; 3; 4; 5; 6; 7; 8; 9; 10}.
FILEIRA(nomes)-FILEIRA(A2)Em seguida, subtraímos o número da linha de A2 de cada valor no array fornecido. Isso nos dá uma matriz de números de série começando em 0. {0; 1; 2; 3; 4; 5; 6; 7; 8}.
FILEIRA(nomes)-FILEIRA(A2)+1: Para obter números de série a partir de 1, adicionamos 1 a cada valor neste array. Isso nos dá o número de série começando em 1. {1; 2; 3; 4; 5; 6; 7; 8; 9}.
Agora temos IF ({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9}). Isso é resolvido como {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}.
Agora temos a fórmula resolvida para SMALL ({1; FALSE; FALSE; FALSE; FALSE; FALSE;7;8;FALSO},2) Agora SMALL retorna o segundo menor valor no intervalo, que é 7.
Como o usamos?
Surge a pergunta: qual é a vantagem de obter um índice bruto da enésima correspondência? Seria mais útil se você pudesse recuperar informações relacionadas a partir do enésimo valor. Bem, isso também pode ser feito. Se quisermos obter o valor do valor da célula adjacente de enésima correspondência no intervalo nomes (A2: A10).
{= ÍNDICE (B2: B10, PEQUENO (SE (nomes = “Rony” ,FILEIRA(nomes)-FILEIRA(A2)+1),2))}
Então, sim pessoal, é assim que vocês podem obter a enésima correspondência em um intervalo. Espero ter sido suficientemente explicativo. Se você tiver alguma dúvida sobre este artigo ou qualquer outro tópico relacionado ao Excel / VBA, escreva na seção de comentários abaixo.
Como obter o número da linha sequencial no Excel
Os 5 principais valores do Vlookup com valores duplicados usando INDEX-MATCH no Excel
VLOOKUP Valores Múltiplos
Use INDEX e MATCH para pesquisar o valor
Valor de pesquisa com vários critérios
Artigos populares:
A função VLOOKUP no Excel
CONT.SE no Excel 2016
Como usar a função SUMIF no Excel