Generieke formule
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Samenvatting
Hoofdlettergevoelige zoekopdrachten in Excel
Standaard zijn standaardzoekopdrachten in Excel niet hoofdlettergevoelig. Zowel VERT.ZOEKEN als INDEX / MATCH retourneren eenvoudig de eerste overeenkomst, waarbij hoofdletters / kleine letters worden genegeerd.
Een directe manier om deze beperking te omzeilen, is door een matrixformule te gebruiken op basis van INDEX / MATCH met EXACT. Als u echter alleen numerieke waarden opzoekt, biedt SOMPRODUCT + EXACT ook een interessante en flexibele manier om hoofdlettergevoelig op te zoeken.
In het voorbeeld gebruiken we de volgende formule
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Hoewel deze formule een matrixformule is, hoeft deze niet te worden ingevoerd met Control + Shift + Enter, aangezien SUMPRODUCT arrays native afhandelt.
Uitleg
SOMPRODUCT is ontworpen om te werken met arrays, die het vermenigvuldigt en vervolgens optelt.
In dit geval zijn we twee arrays met SOMPRODUCT: B3: B8 en C3: C8. De truc is om een test uit te voeren op de waarden in kolom B, en vervolgens de resulterende WAAR / ONWAAR-waarden om te zetten in enen en nullen. We draaien de test met EXACT als volgt:
EXACT(E3,B3:B8)
Welke deze array produceert:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Merk op dat de echte waarde op positie 3 onze match is. Vervolgens gebruiken we het dubbele negatief (dwz -, wat technisch gezien een "dubbele unaire" is) om deze WAAR / ONWAAR waarden in 1 en 0 te dwingen. Het resultaat is deze array:
(0; 0; 1; 0; 0; 0)
Op dit punt in de berekening ziet de SUMPRODUCT-formule er als volgt uit:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT vermenigvuldigt vervolgens eenvoudig de items in elke array om een definitieve array te produceren:
(0; 0; 775; 0; 0; 0)
Welk SOMPRODUCT wordt vervolgens opgeteld en geeft 775 terug.
De kern van deze formule is dus dat de FALSE-waarden worden gebruikt om alle andere waarden te annuleren. De enige waarden die overleven, zijn de waarden die WAAR waren.
Merk op dat omdat we SUMPRODUCT gebruiken, deze formule een unieke twist heeft: als er meerdere overeenkomsten zijn, retourneert SUMPRODUCT de som van die overeenkomsten. Dit kan wel of niet zijn wat je wilt, dus pas op als je meerdere wedstrijden verwacht!
Onthoud dat deze formule alleen werkt voor numerieke waarden, omdat SOMPRODUCT geen tekst verwerkt. Als u tekst wilt ophalen, gebruikt u INDEX / MATCH + EXACT.
