Como resolver o seguinte problema?

Pretendemos procurar o nome competo (nome e apelido) numa tabela onde o nome e o apelido se encontram em colunas distintas.

Vamos utilizar uma fórmula matriz (array formula) para criar uma "tabela virtual" que depois vai ser utilizada dentro da função =Procv( )

Fig. 1: O problema

A partir de uma tabela de dados inicial (ver tabela A2:E10) pretendemos procurar o valor da renda mensal de uma dada pessoa a partir do seu nome completo, neste caso escolhemso o nome "Sofia Martinez".

Vamos utiizar a função =Procv() que nos vai devolver o valor do seu pagamento. 

Mas temos um problema, pois na tabela de dados (A2:E10) o nome e o apelido encontram-se em colunas distintas (coluna A e B). Assim não será possivel utilizar diretamente o Procv()!

Solução:

Podiamos criar uma coluna de apoio onde concatenavamos o nome e o apelido. A seguir utilizariamos essa coluna na função =Procv(). 

Mas não pretendemos criar uma nova coluna na folha de cálculo, em alternativa vamos criar essa nova tabela de forma virtual que irá ter uma nova coluna com o nome e apelido juntos logo no inicio na tabela para que o =Procv() funcione em boas condições. Atenta aos seguintes passos: 

Passo 1: Criar virtualmente uma tabela onde a 1ª coluna vai ter o nome completo, ou seja, com o nome e o apelido na mesma coluna

Primeiro criamos a matriz (array) com o nome completo utilizando esta fórmula:

=ARRAYFORMULA({ $A$2:$A$10 & " " & $B$2:$B$10 })

O simbolo & serve para concatanr cadeias de carateres e as  " " servem para adicionar um espaço entrte o nome e o apelido. 

Como esta formula é uma matriz, devemos depois de digitar a formula carregar em Ctrl + Shift + Enter para adicionar o texto ArrayFormula.

O resultado desta fórmula é uma matriz com os nomes completos. Peparar na célula H2:

Fig. 2: A "matriz virtual" com o nome e apelido juntos

Passo 2: Adicionar as restantes colunas da tabela original

Neste passo vamos adicionar à "tabela virtual" as restantes colunas C, D e E a partir da tabela original. 

Para isso adicionamos à fórmula inicial o seguinte  \ C2:C10 \ D2:D10 \ E2:E10:

=ARRAYFORMULA({ $A$2:$A$10 & " " & $B$2:$B$10 \ C2:C10 \ D2:D10 \ E2:E10 })

As chavetas { … } combinam as matrizes, isso já sabiamos. 

A barra invertida (\) trata os dados como colunas que é o que pretendemos. Reparar nas colunas H, I, J e K:

Fig. 3: A "matriz virtual" com as restantes colunas

Esta fórmula matriz escrita em H2 (array formula) combina as colunas A e B numa unica coluna H e adiciona as colunas C, D e E nesta nova tabela virtual com 4 colunas H, I, J e K.

Passo 3: Executar a procura vertical com a função =Procv()

Agora que já temos a tabela virtual criada, vamos simplesmente utilizá-la dentro da função de procura =Procv() com esta fórmula:

=ARRAYFORMULA(PROCV(G2;{$A$2:$A$10&" "&$B$2:$B$10\C2:C10\D2:D10\E2:E10};2;FALSO()))

que devolve o valor pretendido da renda mensal de 2 333,00 €:

Fig. 4: O resultado final

Nota: Se pretendermos utilizar este =Procv() numa outra linha para procurar outro nome completo, devemos confirmar que as referências são absolutas, para evitar erros.

Este técnica de criação de "tabelas virtuais" que depois são inseridas no interior de outras fórmulas é muito fácil de utilizar uma vez percebendo o funcionamento em matriz.

Podemos tambem utilizar estas "tabelas virtuais" dentro de funções =Filter(), =Query(), etc. 

Ficheiro G. Sheets de apoio (reservado): 

https://docs.google.com/spreadsheets/d/1w52iVEKC_jzU6yEiEGsSqdP0DQPfXSRDu7IRVqji6iU/edit?usp=sharing