Correspondência aproximada para tabelas de faixas

Uma tabela de faixas organiza valores contínuos em intervalos. Alguns exemplos são faixas de imposto, tarifas de envio por peso, descontos por volume e conceitos de letras por pontuação.

Você não precisa ter uma linha para cada valor possível, apenas para o limite inicial de cada faixa. Uma pontuação de 87 não tem uma entrada exata, mas fica na faixa que começa em 80.

É nesse caso que a correspondência aproximada se destaca: ela encontra a faixa correta em vez de exigir uma correspondência exata.

Até agora usamos MATCH(value, range, 0) para uma correspondência exata. O terceiro argumento, 0, significa "encontrar este valor precisamente ou retornar #N/A".

Para faixas, usamos o tipo de correspondência 1. Ele encontra o maior valor menor ou igual a o valor procurado. É exatamente assim que uma busca em faixas deve funcionar.

Uma regra importante: com o tipo de correspondência 1, a lista de limites deve estar ordenada em ordem crescente.

Imagine uma tabela de notas. A coluna E contém os limites inferiores, ordenados em ordem crescente: 0, 60, 70, 80, 90. A coluna F contém os rótulos: F, D, C, B, A.

Uma pontuação de 0 a 59 corresponde a F, de 60 a 69 a D, e assim por diante. Armazenamos apenas o início de cada faixa, não todas as pontuações.

Nosso objetivo é retornar a nota em letra correspondente a uma pontuação em G1.

Use MATCH aproximado para descobrir em qual faixa uma pontuação se encaixa. MATCH(G1, E2:E6, 1), com uma pontuação de 87, procura o maior limite menor ou igual a 87.

Os limites são 0, 60, 70, 80, 90. O maior que não ultrapassa 87 é 80, que está na posição 4. Portanto, MATCH retorna 4.

Essa posição aponta para a faixa correta, mesmo que o próprio 87 não esteja na lista.

Agora passe a posição para INDEX sobre a coluna de rótulos F2:F6.

INDEX(F2:F6, MATCH(G1, E2:E6, 1)) usa a posição 4 e retorna o quarto rótulo, "B".

Assim, uma pontuação de 87 é corretamente associada ao conceito B. Altere G1 para 95 e MATCH retornará 5, resultando em "A"; altere para 55 e MATCH retornará 1, resultando em "F".

MATCH aproximado (tipo 1) exige ordem crescente no intervalo de busca. Ele pressupõe que os dados estejam crescendo e para assim que ultrapassa o valor procurado.

Se os seus limites estiverem fora de ordem, MATCH poderá parar cedo demais e retornar uma posição errada, incorreta e silenciosa, sem nenhum erro para avisar você. Sempre ordene a coluna de limites do menor para o maior antes de confiar em uma busca por faixas.

XLOOKUP também pode fazer correspondências aproximadas. Seu quinto argumento, o modo de correspondência, aceita -1 para "correspondência exata ou o próximo item menor", o que é perfeito para tabelas de faixas.

Isso encontra o maior limite menor ou igual a G1 e retorna o rótulo correspondente, sem precisar de INDEX. Em buscas por faixas, geralmente é mais fácil de ler do que INDEX-MATCH.

Agora, um desconto por volume. Os limites na coluna E (quantidade pedida) são: 0, 10, 50, 100. Os descontos na coluna F são: 0%, 5%, 10%, 15%.

• Pedido de 7 unidades: o maior limite menor ou igual a 7 é 0, na posição 1, retornando 0%.
• Pedido de 60 unidades: o maior limite menor ou igual a 60 é 50, na posição 3, retornando 10%.
• Pedido de 200 unidades: o maior limite menor ou igual a 200 é 100, na posição 4, retornando 15%.

Uma única fórmula funciona para todas as quantidades.

E se um valor for menor que todos os limites? Com MATCH aproximado, não há nenhum valor menor ou igual a ele, então MATCH retorna #N/A.

Para evitar isso, certifique-se de que o primeiro limite cubra o valor mínimo (geralmente 0) ou envolva a fórmula em IFERROR para exibir uma mensagem clara quando uma entrada estiver fora do intervalo.

Fique atento a estas armadilhas das tabelas de faixas:

• Limites não ordenados: a principal causa de resultados errados e silenciosos.
• Usar o tipo de correspondência 0: força uma correspondência exata e retorna #N/A para qualquer valor intermediário.
• Armazenar os finais das faixas em vez dos inícios: MATCH do tipo 1 espera o limite inferior de cada faixa, não o superior.
• Limites como texto: números armazenados como texto prejudicam a comparação; mantenha-os numéricos.

Você pode combinar a correspondência aproximada com a técnica de duas vias. Imagine o custo de envio determinado tanto pela faixa de peso (linhas) quanto pela faixa de região (colunas).

Use um MATCH aproximado (tipo 1) para encontrar a linha do peso e outro para encontrar a coluna da região; depois passe ambos para INDEX. Como os dois eixos contêm limites ordenados, cada MATCH chega à faixa correta.

Isso combina INDEX-MATCH-MATCH com a lógica de faixas para criar tabelas de tarifas detalhadas.

Confirme seu entendimento sobre buscas aproximadas em faixas.

Para buscas em faixas:

• Armazene o limite inferior de cada faixa, em ordem crescente.
• Use MATCH(value, thresholds, 1) para encontrar a posição da faixa (o maior valor menor ou igual à entrada).
• Envolva-o em INDEX(labels, ...) para retornar a faixa ou use XLOOKUP(..., -1) para obter o mesmo resultado.

Cubra o valor mínimo com um limite 0 ou use IFERROR para entradas fora do intervalo, e nunca deixe os limites sem ordenação.