PROCV é uma das funções mais utilizadas no Excel. Ele procura um valor em um intervalo e retorna um valor correspondente em um número de coluna especificado.
Agora me deparei com um problema em que tive que pesquisar a linha inteira e retornar os valores em todas as colunas dessa linha (em vez de retornar um único valor).
Então aqui está o que eu tive que fazer. No conjunto de dados abaixo, eu tinha os nomes dos representantes de vendas e as vendas que eles fizeram em 4 trimestres em 2012. Eu tinha uma lista suspensa com seus nomes e queria extrair o máximo de vendas para esse representante de vendas nesses quatro trimestres.
Eu poderia pensar em 2 maneiras diferentes de fazer isso - usando INDEX ou VLOOKUP.
Pesquisar linha / coluna inteira usando a fórmula INDEX
Aqui está a fórmula que criei para fazer isso usando o índice
= GRANDE (ÍNDICE ($ B $ 4: $ F $ 13, CORRESPONDÊNCIA (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Como funciona:
Vejamos primeiro a função INDEX que está dentro da função LARGE.
= ÍNDICE ($ C $ 4: $ F $ 13, CORRESPONDÊNCIA (H3, $ B $ 4: $ B $ 13,0), 0)
Vamos analisar de perto os argumentos da função INDEX:
- Matriz - $ B $ 4: $ F $ 1
- Número da linha - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Número da coluna - 0
Observe que usei o número da coluna como 0.
O truque aqui é que quando você usa o número da coluna como 0, ele retorna todos os valores em todas as colunas. Portanto, se eu selecionar John no menu suspenso, a fórmula do índice retornará todos os 4 valores de vendas de John {91064,71690,67574,25427}.
Agora posso usar a função Grande para extrair o maior valor
Dica profissional - use o número da coluna / linha como 0 na fórmula do índice para retornar todos os valores nas colunas / linhas.
Pesquisar linha / coluna inteira usando a fórmula VLOOKUP
Enquanto a fórmula do índice é limpa, limpa e robusta, a maneira VLOOKUP é um pouco complexa. Também acaba tornando a função volátil. No entanto, há um truque incrível que eu compartilharia nesta seção. Aqui está a fórmula:
= GRANDE (VLOOKUP (H3, B4: F13, ROW (INDIRETO ("2:" & CONTAGEM ($ B $ 4: $ F $ 4))), FALSO), 1)
Como funciona
- ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))) - Esta fórmula retorna uma matriz {2; 3; 4; 5}. Observe que, como ele usa INDIRETO, isso torna a fórmula volátil.
- VLOOKUP (H3, B4: F13, ROW (INDIRETO (“2:” & CONTAR.VAL ($ B $ 4: $ F $ 4))), FALSO) - Aqui está a melhor parte. Quando você os junta, torna-se PROCV (H3, B4: F13, {2; 3; 4; 5}, FALSO). Agora observe que, em vez de um único número de coluna, dei a ele uma matriz de números de coluna. E VLOOKUP procura obedientemente os valores em todas essas colunas e retorna um array.
- Agora é só usar a função LARGE para extrair o maior valor.
Lembre-se de usar Control + Shift + Enter para usar esta fórmula.
Dica Pro - Em VLOOKUP, em vez de usar um único número de coluna, se você usar uma matriz de números de coluna, ele retornará uma matriz de valores de pesquisa.
