Você fez três perguntas.
Um é: "O VLOOKUP () pode ser usado para fazer isso?" Miroxlav postou uma excelente resposta mostrando como fazer isso usando uma coluna auxiliar.
Sua segunda pergunta pergunta como obter o resultado desejado e a terceira pergunta qual pode ser o motivo da sua fórmula atual retornar a resposta errada. Vou tentar responder os dois últimos.
Sua fórmula está muito próxima de funcionar corretamente. Vamos fazer uma solução de problemas para descobrir o que está errado.
Primeiro, sua fórmula tem alguns erros de digitação - o sinal de multiplicação está faltando em dois lugares entre parênteses. O Excel sugeriu uma correção para isso, e eu removi as referências da folha de dados ('L5 Data'!) Para simplificar:
=INDEX(M5:M15,SUMPRODUCT(--(A5:A15="2018")*(B5:B15="2")*(F5:F15="Filler")*ROW(5:15)))
O Excel tem um recurso de solução de problemas interno você pode destacar parte de uma fórmula na barra de fórmulas e digitar F9 para ver o valor da parte destacada da fórmula.
Clique na célula que contém sua fórmula e, em seguida, destaque a expressão (A5: A15="2018") na barra de fórmulas e pressione F9 (tecla de função 9). Você verá uma matriz contendo todos os valores FALSE . Isto diz que nenhuma célula em A5: A15="2018". Isso é porque você tem 2018 entre aspas. Se você remover as aspas e tentar novamente, obterá a matriz {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} , que é o que você espera - as primeiras 5 células no intervalo são = 2018.
O mesmo problema existe na segunda expressão - remova as marcas de aspas em torno de 2 em (B5:B15="2") . A terceira expressão (F5:F15="Filler") precisa das aspas, porque "Preenchimento" é texto em vez de um número.
Agora destaque (A5:A15=2018)*(B5:B15=2)*(F5:F15="Filler") e clique em F9. Você verá o resultado é {0;0;0;1;0;0;0;0;0;0;0} . O que aconteceu aqui? Multiplicar matrizes de valores lógicos converte valores de True / False em 1 e 0 e também executa o equivalente do AND () lógico. Portanto, este resultado diz que há apenas uma posição no intervalo de linhas 5:15, onde a coluna A é 2018, a coluna B é 2 e a coluna F é "Filler". E essa posição é a quarta.
Em seguida, sua fórmula multiplica essa matriz multiplicando a matriz dos números de linha 5:15. Usando F9, você pode ver esse resultado - {0;0;0;8;0;0;0;0;0;0;0} . Agora, SUMPRODUCT () adiciona os elementos dessa matriz e retorna um resultado de 8, que é o número da linha em que todas as três condições são verdadeiras. Mais uma vez, você pode ver esse resultado usando F9.
E agora aqui está o problema principal - após esses cálculos intermediários, sua fórmula é: =INDEX(M5:M15,8) , que retorna o oitavo elemento do intervalo M5: M15. Mas você não quer o oitavo elemento, você quer o quarto, porque o intervalo M5: M15 começa na linha 5, e 100% está na quarta célula no intervalo M5: M15.
Portanto, a solução é subtrair 4 do SUMPRODUCT ():
=INDEX(M5:M15,SUMPRODUCT((A5:A15=2018)*(B5:B15=2)*(F5:F15="Filler")*ROW(5:15))-4)
Isso retorna o resultado correto - 100%.
Notas:
- Eu não consegui fazer sua fórmula retornar 81,8%, não importa o quanto eu tentei.
- O duplo negativo unário (-) em sua fórmula não é necessário. Geralmente é usado para converter matrizes lógicas em 1's e 0 ', mas a multiplicação das matrizes realiza isso.
Espero que isso ajude. As alterações que descrevi permitem que sua fórmula funcione corretamente. Use a resposta do Miroxlav se você quiser usar o VLOOKUP (). Boa sorte.