Procurar o valor mais próximo com múltiplos critérios

Em alguns casos, poderá precisar de encontrar o valor mais próximo ou aproximado com base em mais do que um critério. Ao combinar as funções ÍNDICE, CORRESP e SE, pode realizar esta tarefa rapidamente no Excel.

Como encontrar o valor mais próximo com base em mais do que um critério?

Conforme ilustrado na imagem abaixo, precisa de encontrar a pessoa certa para o trabalho com base em dois critérios: «a área principal é Computador» e «a experiência profissional é de 15 anos».

Nota: Para funcionar corretamente, se houver áreas principais duplicadas, as respetivas experiências profissionais devem estar ordenadas por ordem crescente.

1. Selecione uma célula vazia para apresentar o resultado, copie a fórmula seguinte para essa célula e prima Ctrl+Shift+Enter para obter o resultado.

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

Notas:nesta fórmula:

• D3:D8é o intervalo de coluna que contém o resultado que está a procurar;
• G5contém o segundo critério (número de experiência 15) com base no qual irá procurar o valor;
• G4contém o primeiro critério (Computador) com base no qual irá procurar o valor;
• B3:B8é o intervalo de células correspondente ao primeiro critério;
• C3:C8é o intervalo de células correspondente ao segundo critério;
• Número 1é uma procura aproximada, o que significa que, se o valor exato não for encontrado, será localizado o maior valor inferior ao valor de procura;
• Esta fórmula tem de ser introduzida como uma fórmula de matriz utilizando as teclas Ctrl+Shift+Enter.

Como funciona esta fórmula

Esta fórmula pode ser dividida em diversos componentes:

• IF(B3:B8=G4,C3:C8): a função SE devolve aqui o resultado {9;13;FALSE;FALSE;FALSE;FALSE}, obtido ao comparar os valores em B3:B8 com o valor em G4. Sempre que há correspondência, devolve o valor respetivo; caso contrário, devolve FALSO. Neste caso, obtém duas correspondências e quatro não correspondências.
• Uma fórmula de matriz =CORRESP(G5,{9,13,FALSO,FALSO,FALSO,FALSO},1): a função CORRESP procura a posição do número 15 (o valor em G5) no intervalo C3:C8. Como o número 15 não é encontrado, corresponde ao próximo valor mais pequeno, que é o 13. Por isso, o resultado aqui é 2.
• E =ÍNDICE(D3:D8;2): a função ÍNDICE devolve o valor da segunda célula no intervalo D3:D8. Assim, o resultado final é Amy.

Funções relacionadas

Artigos relacionados