Resumen
Para traducir valores de texto en números y sumar el resultado, puede usar una fórmula INDICE y COINCIDIR, y la función SUMA. En el ejemplo que se muestra, la fórmula en H5 es:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
donde "código" es el rango con nombre K5: K9 y "valor" es el rango con nombre L5: L9.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Explicación
El corazón de esta fórmula es una fórmula básica de ÍNDICE y COINCIDENCIA, que se utiliza para traducir valores de texto en números como se define en una tabla de búsqueda. Por ejemplo, para traducir "EX" al número correspondiente, usaríamos:
=INDEX(value,MATCH("EX",code,0))
que devolvería 4.
Sin embargo, el giro en este problema es que queremos traducir y sumar un rango de valores de texto en las columnas C a G a números. Esto significa que debemos proporcionar más de un valor de búsqueda y necesitamos que INDICE devuelva más de un resultado. El enfoque estándar es una fórmula como esta:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Después de que se ejecuta MATCH, tenemos una matriz con 5 elementos:
=SUM(INDEX(value,(2,2,3,2,5)))
Entonces parece que INDEX debería devolver 5 resultados a SUM. Sin embargo, si intenta esto, la función INDICE devolverá solo un resultado SUM. Para que INDEX devuelva múltiples resultados, necesitamos usar un truco bastante oscuro y envolver MATCH en N e IF de esta manera:
N(IF(1,MATCH(C5:G5,code,0)))
Esto efectivamente obliga a INDEX a proporcionar más de un valor a la función SUM. Después de que se ejecute INDEX, tenemos:
=SUM((3,3,2,3,-1))
Y la función SUMA devuelve la suma de los elementos de la matriz, 10. Para una buena descripción de este comportamiento, consulte este interesante artículo en el sitio web de EXCELXOR.
