O VLOOKUP, HLOOKUP e INDEX-MATCH são formas famosas e comuns de pesquisar valores em tabelas do Excel. Mas essas não são as únicas maneiras de pesquisar valores no Excel. Neste artigo, aprenderemos como usar a função OFFSET junto com a função MATCH no Excel. Sim, você leu certo, usaremos a famosa função OFFSET do Excel para pesquisar um determinado valor em uma tabela do Excel.
Fórmula Genérica,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Leia com atenção:
StartCell:Esta é a célula inicial da Tabela de pesquisa. Digamos que se você deseja pesquisar no intervalo A2: A10, então o StartCell será A1.
RowLookupValue: Este é o valor de pesquisa que você deseja encontrar nas linhas abaixo doStartCell.
RowLookupRange:Este é o intervalo no qual você deseja pesquisar RowLookupValue. É a faixa abaixo StartCell (A2: A10).
ColLookupValue: Este é o valor de pesquisa que você deseja encontrar nas colunas (cabeçalhos).
ColLookupRange:Este é o intervalo no qual você deseja pesquisar o ColLookupValue. É o intervalo do lado direito de StartCell (como B1: D1).
Então, chega de teoria. Vamos começar com um exemplo.
Exemplo: Consulta de vendas usando a função OFFSET e MATCH da tabela do Excel
Aqui temos uma tabela de amostra de vendas feitas por funcionários diferentes em meses diferentes.
Agora nosso chefe está nos pedindo para dar as vendas feitas pela Id 1004 no mês de junho. Há muitas maneiras de fazer isso, mas como estamos aprendendo sobre a fórmula OFFSET-MATCH, vamos usá-las para pesquisar o valor desejado.
Já temos a fórmula genérica acima. Precisamos apenas identificar essas variáveis nesta tabela.
StartCell é B1 aqui. RowLookupValue é M1. RowLookupRange é B2: B1o (intervalo abaixo da célula inicial).
ColLookupValue está na célula M2. ColLookupRange é C1: I1 (Intervalo de cabeçalho à direita de StartCell).
Identificamos todas as variáveis. Vamos colocá-los em uma fórmula do Excel.
Escreva esta fórmula na célula M3 e pressione o botão Enter.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Ao apertar o botão Enter, você obtém o resultado prontamente. A venda feita ID 1004 no mês de junho foi de 177.
Como funciona?
O trabalho da função OFFSET é mover-se de uma determinada célula inicial para uma determinada linha e colunas e, em seguida, retornar o valor dessa célula. Por exemplo, se eu escrever OFFSET (B1,1,1), a função OFFSET irá para a célula C2 (1 célula para baixo, 1 célula para a direita) e retorna seu valor.
Aqui, nós temos fórmulaOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
A primeira função MATCH retorna o índice de M1 (1004) no intervalo B2: B10, que é 4. Agora a fórmula é,OFFSET (B1,4, MATCH (M2, C1: I1,0)).
A próxima função MATCH retorna o índice de M2 ('Jun') no intervalo C1: I1, que i 7. Agora a fórmula éOFFSET (B1,4,7).
Agora a função OFFSET simplesmente move 4 células para baixo para a célula B1 que a leva para B5. Em seguida, a função OFFSET move-se para 7 da esquerda para B5, que o leva para I5. É isso. A função OFFSET retorna o valor da célula I5 que 177.
Vantagens dessa técnica de pesquisa?
Isso é rápido. A única vantagem desse método é que ele é mais rápido do que os outros métodos. A mesma coisa pode ser feita usando a função INDEX-MATCH ou a função VLOOKUP. Mas é bom conhecer algumas alternativas. Pode ser útil algum dia.
Então sim pessoal, é assim que você pode usar as funções OFFSET e MATCH para os valores de LOOKUP nas tabelas do Excel. Espero que tenha sido explicativo o suficiente. Se você tiver alguma dúvida sobre este artigo ou qualquer outro tópico relacionado ao Excel / VBA, pergunte-me na seção de comentários abaixo.
Use INDEX e MATCH para pesquisar o valor:A fórmula INDEX-MATCH é usada para pesquisar de forma dinâmica e precisa um valor em determinada tabela. Esta é uma alternativa para a função VLOOKUP e supera as deficiências da função VLOOKUP.
Soma por grupos OFFSET em linhas e colunas: A função OFFSET pode ser usada para somar grupos de células dinamicamente. Esses grupos podem estar em qualquer lugar da planilha.
Como recuperar cada enésimo valor em um intervalo no Excel: Usando a função OFFSET do Excel, podemos recuperar valores de linhas ou colunas alternadas. Esta fórmula usa a ajuda da função ROW para alternar nas linhas e da função COLUMN para alternar nas colunas.
Como usar a função OFFSET no Excel: A função OFFSET é uma função Excel poderosa que é subestimada por muitos usuários. Mas os especialistas conhecem o poder da função OFFSET e como podemos usar esta função para fazer algumas tarefas mágicas na fórmula do Excel. Aqui estão os fundamentos da função OFFSET.
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar a função VLOOKUP do Excel| Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar o Excel Função CONT.SE| Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função CONT.SE é essencial para preparar seu painel.
Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.