Função ENDEREÇO (e como utilizá-la de uma forma que você nunca viu)

As funções de Pesquisa e Referência do Excel (como a função ENDEREÇO,  por exemplos), nos ajudam a trazer a informação que está em determinado lugar.

Se quero, por exemplo, a receita de um produto que está na Planilha X, eu deveria fazer um PROCV para que, com o nome do Produto, eu possa trazer a Receita dele.

Mas imagine a situação na qual queiramos IR ATÉ onde este ítem está. Isto é, ao invés de trazer a receita do produto A, vamos IR até onde este produto A se localiza.

Para que isto seja possível, vamos combinar a função ENDEREÇO com CORRESP e HIPERLINK.

A função HIPERLINK servirá para nos dar o link de onde este ítem estará.

A função CORRESP servirá para nos dar a posição do ítem que desejamos procurar

A função ENDEREÇO servirá para nos trazer o endereço (linha/coluna) de onde está este ítem.

Vejamos como está nossa planilha (você pode baixá-la AQUI):

Temos neste exemplo uma lista com cidades brasileiras com nomes “curiosos” (esta planilha chama-se “dados”.

Em outra planilha, de nome “consulta”, temos o seguinte:

Queremos digitar o nome de uma cidade na célula B5 e fazer com que na B6 apareça um hiperlink que nos envie para onde o nome desta cidade se encontra.

PARTE 1 – Criar validação de dados

Para garantir que digitemos o nome da cidade corretamente, vamos criar na célula B5 uma validação de dados, com a seguinte configuração:

Assim podemos simplesmente selecionar uma cidade na listagem. Neste exemplo, vamos selecionar a cidade de “Feliz Natal”:

PARTE 2 – Função CORRESP

Vamos começar a inserir na célula B6 a função. Vamos fazer por partes, começando com a função CORRESP.

Ela nos informará em que linha a cidade selecionada se encontra. Sua sintaxe ficará assim:

=CORRESP(B5;dados!$A$1:$A$26;0)

Vamos procurar o nome da cidade (que está na B5), na lista de cidades (que está na planilha DADOS entre a A1 e a A26 – é importante marcarmos desde a linha 1, porque queremos a referência da LINHA onde a cidade está e não sua posição relativa na lista).

A resposta disto será 10, já que a cidade de Feliz Natal se encontra na linha 10 da planilha Dados.

PARTE 3 – Função ENDEREÇO

A função ENDEREÇO nos traz como resposta uma referência de célula, do tipo A1, B10, etc. Significa que ela não vai trazer diretamente a informação, se sim a posição onde a informação desejada está.

A sintaxe da função ENDEREÇO é:

• Número da Linha
• Número da Coluna
• Tipo de referência (se linha e/ou coluna são relativos ou absolutos)
• Tipo de notação (se A1 ou L!C1)
• Nome da Planilha

No nosso exemplo, teremos:

• Número da Linha: resultado do CORRESP, no caso, 10
• Número da Coluna: 1, já que a lista de cidades encotnra-se na coluna A (no caso, 1)
• Tipo de referência (se linha e/ou coluna são relativos ou absolutos): neste caso, tanto faz. vamos deixar como 1, que signific linha e coluna absoloutos (o $ aparece na linha e na coluna)
• Tipo de notação (se A1 ou L1C1): deixaremos na forma mais comum, o que significa o código 1
• Nome da Planilha: “Dados”. Se fosse na mesma planilha poderia deixar este campo em branco.

Nossa função, agora combinando a função ENDEREÇO com CORRESP. ficará:

=ENDEREÇO(CORRESP(B5;dados!$A$1:$A$26;0);1;1;1;”dados”)

Se olharmos a resposta, ela será dados!$A$10, porque, como informamos antes, ela retorna apenas o endereço onde o que queremos está.

PARTE 4 – Função HIPERLINK

É agora onde a função HIPERLINK entra, já que precisamos informar nela a célula para onde a referência deve ir.

Queremos que ela nos leve exatamente para a célula A10 da planilha DADOS.

Então vamos adicioná-la a nossa função. Ficará assim:

=HIPERLINK(“#”&ENDEREÇO(CORRESP(B5;dados!$A$1:$A$26;0);1;1;1;”dados”);B5)

Vamos concatenar o “#” (que é necessário quando queremos ir para uma célula usando o hiperlink – veja mais neste link), com a referência de célula para onde queremos ir.

O 2º argumento da função é o nome que queremos que apareça na célula. Vamos ver o nome da cidade selecionada.

Agora, basta selecionar uma cidade na B5 para que apareça o hiperlink nos levando diretamente para onde aquela cidade está.