Resumo
Para traduzir valores de texto em números e somar o resultado, você pode usar uma fórmula INDEX e MATCH e a função SUM. No exemplo mostrado, a fórmula em H5 é:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
onde "código" é o intervalo nomeado K5: K9 e "valor" é o intervalo nomeado L5: L9.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
O coração desta fórmula é uma fórmula básica INDEX e MATCH, usada para traduzir valores de texto em números, conforme definido em uma tabela de pesquisa. Por exemplo, para traduzir "EX" para o número correspondente, usaríamos:
=INDEX(value,MATCH("EX",code,0))
que retornaria 4.
A diferença neste problema, entretanto, é que queremos traduzir e somar uma gama de valores de texto nas colunas C a G em números. Isso significa que precisamos fornecer mais de um valor de pesquisa e precisamos INDEX para retornar mais de um resultado. A abordagem padrão é uma fórmula como esta:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Após a execução do MATCH, temos uma matriz com 5 itens:
=SUM(INDEX(value,(2,2,3,2,5)))
Portanto, parece que INDEX deve retornar 5 resultados para SUM. No entanto, se você tentar isso, a função INDEX retornará apenas um resultado SUM. Para fazer com que INDEX retorne vários resultados, precisamos usar um truque bastante obscuro e envolver MATCH em N e IF assim:
N(IF(1,MATCH(C5:G5,code,0)))
Isso efetivamente força INDEX a fornecer mais de um valor para a função SUM. Após a execução de INDEX, temos:
=SUM((3,3,2,3,-1))
E a função SUM retorna a soma dos itens na matriz, 10. Para uma boa descrição desse comportamento, consulte este artigo interessante no site da EXCELXOR.
