ÍNDICE + CORRESP com Múltiplios Critérios

Quando temos uma tabela com mais de uma informação nas linhas ou nas colunas, pode surgir uma dúvida em como prosseguir com as funções de busca. Vejamos o caso abaixo, por exemplo:

Aqui, temos a evolução histórica do preço por quilograma para cada "Loja" e "Produto". Muitas vezes, fazemos uso das funções ÍNDICE e CORRESP quando temos apenas uma informação no início das linhas e outra no topo das colunas, mas como buscar o preço do kg do tomate na loja B no dia 29/fev/2020 com uma expressão?

Podemos aplicar as funções ÍNDICE e CORRESP de duas formas:

{=ÍNDICE(C2:F7;CORRESP(1;(A2:A7=H2)*(B2:B7=I2);0);CORRESP(J2;C1:F1;0))}

(por verificação booleana)

ou

{=ÍNDICE(C2:F7;CORRESP(H2&I2;A2:A7&B2:B7;0);CORRESP(J2;C1:F1;0))}

(por concatenação)

Em ambos os casos, no primeiro argumento da função ÍNDICE, é indicada a matriz de valores, que seria o conjunto C2:F7 e, no terceiro argumento, a função CORRESP é aplicada de forma usual, buscando a data (J2) no intervalo C1:F1 com correspondência exata, retornando 2, uma vez que 29/fev/2020 é a 2ª data do cabeçalho.

Método Booleano

No primeiro caso, foi utilizada a lógica booleana, onde as igualdades entre os cabeçalhos e os valores pretendidos retornam vetores com 0 e 1. Neste caso:

(A2:A7=H2)={FALSO;FALSO;FALSO;VERDADEIRO;VERDADEIRO;VERDADEIRO}

verifica a correspondência do campo "Loja"

(B2:B7=I2)={VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO;FALSO}

verifica a correspondência do campo "Produto"

E o resultado do produto será 1 somente na posição em que há VERDADEIRO em ambos os vetores acima:

(A2:A7=H2)*(B2:B7=I2)={0;0;0;1;0;0}

Com a função CORRESP, buscando o valor 1 no  intervalo acima, obtém-se 4, que corresponde à posição da linha em que a loja é B e o produto é tomate. Assim, a função ÍNDICE buscará o elemento na 4ª linha e 2ª coluna da matriz C2:F7, ou seja, o preço de 5,49 por quilograma.

Concatenação

Outra forma de resolver este problema seria concatenando os campos "Loja" e "Produto". Desta forma, ao invés de buscar "B" no campo "Loja" e "Tomate" no campo "Produto" separadamente, buscamos "BTomate" no vetor resultante da junção de "Loja"&"Produto". Nas expressões:

H2&I2="BTomate"

e

A2:A7&B2:B7={"ATomate";"APepino";"ARabanete";"BTomate";"BPepino";"BRabanete"}

Sendo equivalente somente na posição 4 (resultado do CORRESP) e fazendo com que a função ÍNDICE retorne 5,49 também.

Para ter acesso aos dados deste exemplo, clique aqui.

Tem alguma dúvida, crítica ou sugestão? Comente aí embaixo!

Funções de Busca PROCV e ÍNDICE + CORRESP

Para quem trabalha com o Microsoft Excel, é muito comum se deparar com a necessidade de buscar valores em um conjunto de dados. Por exemplo, na tabela abaixo, temos o PIB (Produto Interno Bruto) nominal referente a 2018 de alguns países (em milhões de dólares americanos), assim como sua posição no ranking e a região geográfica:

Vamos supor que precisemos buscar rapidamente o país cuja posição no ranking é a de #5, por meio de uma fórmula. Isso pode ser feito com a função PROCV da seguinte forma:

No caso do PROCV, inserimos como parâmetros na função:

1. Valor procurado - neste caso, buscamos o país com ranking #5 e, portanto, será necessário buscar o número 5 na primeira coluna da tabela do exemplo (Posição);
2. Matriz/tabela - aqui, indicamos a tabela que contém as informações que nós temos (Posição) e as que desejamos (País), que, neste caso, é o conjunto A1:D31 (o valor procurado, a posição, deve estar na primeira coluna desta tabela);
3. Coluna do resultado desejado - como a função deve retornar o país correspondente ao ranking #5 e os nomes dos países estão na 3ª coluna da tabela selecionada, devemos indicar o número 3;
4. Tipo correspondência - se a correspondência precisa ser exata, é necessário inserir FALSO (ou 0), caso contrário, se a correspondência pode ser aproximada, devemos inserir VERDADEIRO (ou 1). Aqui, queremos um resultado exato e inserimos o 0.

Com isso, o resultado seria "Reino Unido", como esperado:

Uma outra maneira de resolver este problema seria por meio da popular combinação das funções ÍNDICE e CORRESP:

Começando de dentro para fora, a função CORRESP resulta na posição de um elemento em um vetor (linha ou coluna). Neste caso, desejamos localizar em qual linha está o país de ranking #5, com os seguintes parâmetros:

1. Valor procurado - assim como na função PROCV, devemos indicar o valor que estamos buscando, que aqui é o número 5;
2. Matriz procurada - estamos buscando o número 5 na coluna "Posição", que no caso é a primeira da tabela, ou seja, o intervalo A1:A31;
3. Tipo correspondência - assim como no caso do PROCV, optaremos pela correspondência exata, inserindo o número 0.

Neste ponto, a função CORRESP retornará 6 como resultado, pois o número 5 está na 6ª posição do conjunto A1:A31.

Em seguida, faremos uso da função ÍNDICE, com os inputs:

1. Matriz - o range do resultado desejado, que, neste caso, é a coluna de países, no intervalo C1:C31;
2. Número da linha - que é a posição no intervalo C1:C31 do elemento que queremos retornar, indicada pela função CORRESP.

Com isso, também obtemos o resultado "Reino Unido":

Para obter a planilha do exemplo, clique aqui.

Tem alguma dúvida, crítica ou sugestão? Comente aí embaixo!