Tutorial do Excel: pesquisa bidirecional com INDEX e MATCH aproximados

Índice

Atalhos relacionados

Neste vídeo, veremos como construir uma pesquisa bidirecional com INDEX e MATCH, usando uma correspondência aproximada.

Aqui temos uma calculadora de custos simples, que analisa os custos com base na largura e altura do material. A correspondência precisa ser aproximada. Por exemplo, se a largura for 250 e a altura 325, o resultado correto é $ 1.800.

Se a largura for 450 e a altura permanecer em 325, o resultado correto é $ 3.600.

Podemos construir uma fórmula que faça essa pesquisa usando INDEX e MATCH.

Primeiro, vamos fazer o INDEX funcionar como uma prova de conceito, codificando parte da fórmula. Essa é uma ótima maneira de ter certeza de que você tem a ideia certa antes de começar.

Então, com os dados em nossa tabela como a matriz, e com uma largura de 450 e uma altura de 325, INDEX vai precisar de um número de linha de 3 e de um número de coluna de 4 para recuperar o valor correto na tabela . Isso funciona bem, mas é claro que não vai mudar, pois os valores são codificados.

A seguir, vamos configurar as funções MATCH de que precisamos para calcular esses valores.

Para obter o valor de largura, que é o número da linha em INDEX, usaremos o valor de pesquisa de M7 e os valores da coluna B como a matriz de pesquisa. Para o tipo de correspondência, queremos usar 1 para correspondência aproximada porque os valores são classificados em ordem crescente. O resultado é 4.

Para obter altura, que é a coluna dentro de INDEX, usaremos novamente MATCH com o valor de M8, os valores de alturas da linha 6. Novamente, o tipo de correspondência novamente definido como 1 para correspondência aproximada. O resultado é 3.

Agora, se eu alterar a largura para 350 e a altura para 550, obteremos um novo conjunto de resultados.

Esses valores são exatamente o que precisamos para INDEX. Agora, simplesmente copiarei e colarei as funções MATCH na fórmula INDEX original.

A largura entra no número da linha.

E a altura vai para o número da coluna.

Agora temos uma pesquisa dinâmica que calcula corretamente o custo com base na largura e altura, com correspondência aproximada.