Se você já está lendo as dicas do Excel há algum tempo, invariavelmente encontrou alguém que fala sobre o uso das funções INDEX () & MATCH () do Excel em vez das funções VLOOKUP do Excel. Falando por mim, sempre foi muito difícil tentar dominar DUAS novas funções simultaneamente. Mas, é um truque legal. Dê-me cinco minutos e tentarei explicar em um inglês simples.
A revisão de 30 segundos de VLOOKUP
Digamos que você tenha uma tabela de registros de funcionários. A primeira coluna é um número de funcionário e as colunas restantes são vários dados sobre o funcionário. Sempre que tiver um número de funcionário na planilha, você pode usar VLOOKUP para retornar um dado específico sobre o funcionário. A sintaxe é VLOOKUP (valor, intervalo de dados, número da coluna, FALSO). Diz ao Excel: "Vá para o intervalo de dados. Encontre uma linha que tenha (valor) na primeira coluna do intervalo de dados. Retorne o (número da coluna) o valor dessa linha. Depois de pegar o jeito, é muito simples e poderoso.
O problema
Um dia, você tem uma situação em que tem o nome do funcionário, mas precisa do número do funcionário. Na imagem a seguir, você tem um nome em A10 e precisa encontrar o número do funcionário em B10.
Quando o campo-chave está à direita dos dados que você deseja recuperar, VLOOKUP não funcionará. Se apenas PROCV aceitar -1 como o número da coluna, não haverá problema. Mas não é. Uma solução comum é inserir temporariamente uma nova coluna A, copiar a coluna de nomes para a nova coluna A, preencher com VLOOKUP, Colar valores especiais e, em seguida, excluir a coluna temporária A. Os profissionais do Excel provavelmente podem fazer isso durante o sono.
Vou sugerir que você aceite o desafio e tente usar esse método de etapa única. Sim, você terá que pregar a fórmula na parede por algumas semanas, mas você fez isso com VLOOKUP há muito tempo, também, não foi?
Acho que isso é tão difícil porque você está usando duas funções que provavelmente nunca usou antes. Então, deixe-me dividir em duas partes.
Primeiro, existe a função INDEX (). Esta é uma função com um nome horrível. Quando alguém diz "índice", isso não evoca nada em minha mente que seja semelhante ao que esta função faz. O índice requer três argumentos.
=INDEX(data range, row number, column number)
Em inglês, o Excel vai para o intervalo de dados e retorna o valor na interseção da (número da linha) ésima linha e (número da coluna) ésima coluna. Ei, pense sobre isso - isso é muito simples, certo? =INDEX($A$2:$C$6,4,2)fornecerá o valor em B5.
Aplicando INDEX () para o nosso problema, você pode descobrir que para retornar o número de funcionário do intervalo, você usaria o seguinte: =INDEX($A$2:$A$6,?,1). Na verdade, essa parte parece tão trivial que parece inútil. Mas, quando você substitui o ponto de interrogação por uma função MATCH (), você tem a solução.
Aqui está a sintaxe:
=MATCH(Value, Single-column data range, FALSE)
Diz ao Excel: "Pesquise o intervalo de dados e diga-me o número da linha relativa onde encontra uma correspondência para (dados). Portanto, para descobrir qual linha tem o funcionário em A10, você usaria =MATCH(A10,$B$2:$B$6,FALSE). Sim, é mais complexo do que Índice , mas deve ser exatamente o beco de PROCV PRO. Se A10 contiver "Miller, Bob", então este MATCH retornará que ele está na 3ª linha do intervalo B2: B6.
Aí está - a função MATCH () diz à função Index em qual linha procurar - você terminou. Pegue a função Índice, substitua nosso ponto de interrogação pela função CORRESPONDÊNCIA e agora você pode fazer o equivalente a VLOOKUPs quando o campo-chave não está na coluna esquerda. Aqui está a função a ser usada:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
O post-it na minha parede realmente mostra duas linhas. Primeiro, escrevi a explicação para MATCH (). Abaixo disso escrevi a explicação para INDEX (). Em seguida, desenhei uma forma de funil entre os dois para indicar que a função MATCH () cai no segundo argumento da função INDEX ().
Nas primeiras vezes que tive que fazer um desses, fiquei tentado apenas a colocar uma nova coluna A temporária ali, mas passei pela dor de fazer isso dessa forma. É mais rápido e requer menos manipulação. Então, da próxima vez que você desejar colocar um número negativo na função VLOOKUP, tente esta estranha combinação de INDEX e MATCH para resolver seus problemas.
