Pesquisas bidirecionais com INDEX-MATCH-MATCH

Imagine uma grade de vendas mensais em que as regiões aparecem à esquerda e os meses se estendem no topo. Você quer o número no ponto em que uma região escolhida encontra um mês escolhido.

Uma busca normal encontra um valor pesquisando em apenas uma direção. Uma busca em duas direções pesquisa em ambas as direções ao mesmo tempo: encontra a linha e a coluna corretas e depois retorna o valor na interseção delas.

A ferramenta clássica para isso é INDEX combinada com duas chamadas de MATCH, geralmente escrita como INDEX-MATCH-MATCH.

INDEX retorna um valor de um intervalo pela sua posição. A forma completa é INDEX(array, row_num, column_num).

Forneça um bloco de células, um número de linha e um número de coluna, e ela devolverá o valor nesse ponto. Por exemplo, em uma grade que começa em B2, solicitar a linha 3 e a coluna 2 retorna o valor localizado 3 linhas abaixo e 2 colunas à direita dentro desse bloco.

A ideia principal: INDEX precisa de posições, não de rótulos. É exatamente isso que MATCH fornece.

MATCH encontra a posição de um valor dentro de uma única linha ou coluna. Sua forma é MATCH(lookup_value, lookup_array, match_type).

Use 0 como tipo de correspondência para uma correspondência exata. O resultado é um número: a posição do valor, contando a partir de 1.

Se "East" for o segundo item do intervalo A2:A5, MATCH retornará 2. Esse 2 pode se tornar o número da linha para INDEX.

Para fazer uma busca em duas direções, execute MATCH duas vezes:

• Uma MATCH encontra em qual linha está a região.
• Uma MATCH encontra em qual coluna está o mês.

Depois, forneça os dois números a INDEX. A MATCH da linha pesquisa um intervalo vertical de rótulos; a MATCH da coluna pesquisa um intervalo horizontal de cabeçalhos.

O resultado é a única célula onde essa linha e essa coluna se cruzam.

Imagine este layout. Os rótulos das regiões estão em A2:A5 (East, West, North, South). Os cabeçalhos dos meses estão em B1:D1 (Jan, Feb, Mar). Os números reais de vendas preenchem B2:D5.

Duas células de entrada controlam a busca: G1 contém a região desejada e G2 contém o mês desejado.

Nosso objetivo: uma única fórmula que leia G1 e G2 e retorne o valor de vendas correspondente de B2:D5.

Primeiro, localize a região. MATCH pesquisa o valor digitado em G1 na lista vertical de rótulos A2:A5.

Se G1 contiver "North" e North for o terceiro rótulo, esta MATCH retornará 3.

Esse número informa a INDEX qual linha do bloco de dados deve ler. Observe que pesquisamos A2:A5, apenas os rótulos, não os dados; assim, a posição 3 corresponde à terceira linha de dados.

Em seguida, localize o mês. Esta MATCH pesquisa o valor em G2 na linha horizontal de cabeçalhos B1:D1.

Se G2 contiver "Feb" e Feb for o segundo cabeçalho, MATCH retornará 2.

Esse número se torna a posição da coluna para INDEX. Assim como no caso das linhas, pesquisamos apenas os cabeçalhos B1:D1, para que a posição corresponda às colunas de dados em B2:D5.

Agora envolva as duas chamadas de MATCH dentro de INDEX. O bloco de dados B2:D5 é a matriz; a MATCH da linha fornece o número da linha, e a MATCH da coluna fornece o número da coluna.

Quando G1 é "North" e G2 é "Feb", a MATCH da linha fornece 3 e a MATCH da coluna fornece 2; portanto, INDEX retorna o valor na linha 3, coluna 2 de B2:D5.

Esta única fórmula é a busca completa em duas direções.

Suponha que B2:D5 contenha: a linha de North é Jan 50, Feb 80, Mar 65.

• MATCH("North", A2:A5, 0) retorna 3.
• MATCH("Feb", B1:D1, 0) retorna 2.
• INDEX(B2:D5, 3, 2) lê a linha 3, coluna 2, retornando 80.

Altere G1 para "East" ou G2 para "Mar" e toda a fórmula será recalculada instantaneamente. Esse é o poder de usar dois resultados de busca MATCH para conduzir INDEX.

VLOOKUP pesquisa apenas a primeira coluna e retorna um valor localizado a um número fixo de colunas à direita. Para trocar os meses, seria necessário definir diretamente ou calcular por conta própria o índice da coluna.

INDEX-MATCH-MATCH permite que tanto a linha quanto a coluna sejam escolhidas dinamicamente pelo rótulo. Reordene as colunas ou insira novos meses, e a fórmula continuará funcionando, pois faz a correspondência com o texto do cabeçalho, não com um número fixo.

O erro mais comum é usar intervalos incompatíveis. O intervalo de MATCH para a linha deve ter a mesma altura do bloco de dados de INDEX, e o intervalo de MATCH para a coluna deve ter a mesma largura.

Aqui, A2:A5 tem 4 linhas de altura e B2:D5 também tem 4 linhas de altura; portanto, um resultado 3 de MATCH realmente significa a terceira linha de dados. Se você pesquisar acidentalmente A1:A5, que inclui um cabeçalho, as posições serão deslocadas em uma linha e você obterá a célula errada.

Teste sua compreensão do padrão de busca em duas direções.

Você aprendeu o padrão de busca em duas direções:

• INDEX retorna um valor em uma posição de linha e coluna dentro de um bloco.
• Um MATCH encontra a linha pesquisando rótulos verticais.
• Um segundo MATCH encontra a coluna pesquisando cabeçalhos horizontais.

A fórmula combinada =INDEX(data, MATCH(row), MATCH(col)) lê duas entradas e retorna o valor na interseção delas. Mantenha os intervalos de MATCH do mesmo tamanho que o bloco de dados para evitar desalinhamentos.