Resumo
Para realizar uma pesquisa de correspondência aproximada bidirecional com vários critérios, você pode usar uma fórmula de matriz baseada em INDEX e MATCH, com ajuda da função IF para aplicar os critérios. No exemplo mostrado, a fórmula em K8 é:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
onde os dados (D6: H16), diâmetro (D5: H5), material (B6: B16) e dureza (C6: C16) são intervalos nomeados usados apenas por conveniência.
Observação: esta é uma fórmula de matriz e deve ser inserida com Control + Shift + Enter
Explicação
O objetivo é pesquisar uma taxa de alimentação com base no material, dureza e diâmetro da broca. Os valores da taxa de alimentação estão nos dados do intervalo nomeado (D6: H16).
Isso pode ser feito com uma fórmula INDEX e MATCH bidirecional. Uma função MATCH calcula o número da linha (material e dureza), e a outra função MATCH encontra o número da coluna (diâmetro). A função INDEX retorna o resultado final.
No exemplo mostrado, a fórmula em K8 é:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Quebras de linha adicionadas apenas para legibilidade).
A parte complicada é que o material e a dureza precisam ser tratados juntos. Precisamos restringir MATCH aos valores de dureza de um determinado material (Aço de Baixo Carbono no exemplo mostrado).
Podemos fazer isso com a função IF. Essencialmente, usamos IF para "jogar fora" valores irrelevantes antes de procurar uma correspondência.
Detalhes
A função INDEX recebe os dados de intervalo nomeados (D6: H16) como para array. A primeira função MATCH calcula o número da linha:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Para localizar a linha correta, precisamos fazer uma correspondência exata no material e uma correspondência aproximada na dureza. Fazemos isso usando a função IF para primeiro filtrar a dureza irrelevante:
IF(material=K5,hardness) // filter
Testamos todos os valores no material (B6: B16) para ver se eles correspondem ao valor em K5 ("Aço de baixo carbono"). Nesse caso, o valor de dureza é passado. Caso contrário, IF retorna FALSE. O resultado é uma matriz como esta:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Observe que os únicos valores remanescentes são aqueles associados ao aço de baixo carbono. Os outros valores agora são FALSE. Este array é retornado diretamente para a função MATCH como lookup_array.
O valor de pesquisa para correspondência vem de K6, que contém a dureza fornecida, 176. MATCH é configurado para correspondência aproximada definindo match_type como 1. Com essas configurações, MATCH ignora os valores FALSE e retorna a posição de uma correspondência exata ou o próximo menor valor .
Nota: os valores de dureza devem ser classificados em ordem crescente para cada material.
Com a dureza fornecida como 176, MATCH retorna 6, fornecido diretamente para INDEX como o número da linha. Agora podemos reescrever a fórmula original assim:
=INDEX(data,6,MATCH(K7,diameter,1))
A segunda fórmula MATCH encontra o número correto da coluna realizando uma correspondência aproximada no diâmetro:
MATCH(K7,diameter,1) // get column num
Nota: os valores em diâmetro D5: H5 devem ser classificados em ordem crescente.
O valor de pesquisa vem de K7 (0,75), e lookup_array é o diâmetro de intervalo nomeado (D5: H5).
Como antes, o MATCH é definido para correspondência aproximada definindo match_type como 1.
Com o diâmetro dado como 0,75, MATCH retorna 3, enviado diretamente para a função INDEX como o número da coluna. A fórmula original agora resolve para:
=INDEX(data,6,3) // returns 0.015
INDEX retorna um resultado final de 0,015, o valor de F11.
