Samenvatting
Als u tekstwaarden in getallen wilt vertalen en het resultaat wilt optellen, kunt u een INDEX- en MATCH-formule en de functie SOM gebruiken. In het getoonde voorbeeld is de formule in H5:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
waarbij "code" het benoemde bereik K5: K9 is, en "waarde" het benoemde bereik L5: L9.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
Het hart van deze formule is een basisformule INDEX en MATCH, die wordt gebruikt om tekstwaarden te vertalen naar getallen zoals gedefinieerd in een opzoektabel. Om bijvoorbeeld "EX" naar het corresponderende nummer te vertalen, gebruiken we:
=INDEX(value,MATCH("EX",code,0))
die zou terugkeren 4.
De twist in dit probleem is echter dat we een reeks tekstwaarden in de kolommen C tot en met G willen vertalen en optellen in getallen. Dit betekent dat we meer dan één opzoekwaarde moeten opgeven en dat we INDEX nodig hebben om meer dan één resultaat te retourneren. De standaardaanpak is een formule als deze:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Nadat MATCH is uitgevoerd, hebben we een array met 5 items:
=SUM(INDEX(value,(2,2,3,2,5)))
Het lijkt er dus op dat INDEX 5 resultaten moet retourneren naar SUM. Als u dit echter probeert, retourneert de functie INDEX slechts één resultaat SUM. Om INDEX meerdere resultaten te laten retourneren, moeten we een nogal obscure truc gebruiken en MATCH in N en IF als volgt omwikkelen:
N(IF(1,MATCH(C5:G5,code,0)))
Dit dwingt INDEX effectief om meer dan één waarde aan de SOM-functie te geven. Nadat INDEX is uitgevoerd, hebben we:
=SUM((3,3,2,3,-1))
En de functie SOM retourneert de som van de items in de array, 10. Zie dit interessante artikel op de EXCELXOR-website voor een goed overzicht van dit gedrag.
