Generieke formule
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Samenvatting
Om waarden op te zoeken met INDEX en MATCH met behulp van meerdere criteria, kunt u een matrixformule gebruiken. In het getoonde voorbeeld is de formule in H8:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Let op: dit is een matrixformule en moet worden ingevoerd met control + shift + enter, behalve in Excel 365.
Uitleg
Dit is een meer geavanceerde formule. Zie INDEX en MATCH gebruiken voor de basisprincipes.
Normaal gesproken is een INDEX MATCH-formule geconfigureerd met MATCH die is ingesteld om door een bereik van één kolom te kijken en een overeenkomst te bieden op basis van bepaalde criteria. Zonder waarden in een hulpkolom of in de formule zelf samen te voegen, is er geen manier om meer dan één criterium op te geven.
Deze formule omzeilt deze beperking door booleaanse logica te gebruiken om een array van enen en nullen te maken om rijen weer te geven die aan alle 3 criteria voldoen, en vervolgens MATCH te gebruiken om de eerste gevonden 1 te matchen. De tijdelijke reeks van enen en nullen wordt gegenereerd met dit fragment:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Hier vergelijken we het artikel in H5 met alle artikelen, de maat in H6 met alle maten en de kleur in H7 met alle kleuren. Het eerste resultaat is drie arrays van WAAR / ONWAAR resultaten zoals deze:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tip: gebruik F9 om deze resultaten te zien. Selecteer gewoon een uitdrukking in de formulebalk en druk op F9.
De wiskundige bewerking (vermenigvuldiging) zet de TRUE FALSE-waarden om in 1s en 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Na vermenigvuldiging hebben we een enkele array zoals deze:
(0;0;1;0;0;0;0)
die wordt ingevoerd in de MATCH-functie als de lookup-array, met een lookup-waarde van 1:
MATCH(1,(0;0;1;0;0;0;0))
Op dit punt is de formule een standaard INDEX MATCH-formule. De MATCH-functie retourneert 3 naar INDEX:
=INDEX(E5:E11,3)
en INDEX retourneert een eindresultaat van $ 17,00.
Matrix visualisatie
De hierboven beschreven arrays kunnen moeilijk te visualiseren zijn. De onderstaande afbeelding toont het basisidee. Kolommen B, C en D komen overeen met de gegevens in het voorbeeld. Kolom F wordt gemaakt door de drie kolommen met elkaar te vermenigvuldigen. Het is de array die aan MATCH is overgedragen.
Niet-array-versie
Het is mogelijk om nog een INDEX aan deze formule toe te voegen, zodat u niet als matrixformule hoeft in te voeren met control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
De INDEX-functie kan native arrays behandelen, dus de tweede INDEX wordt alleen toegevoegd om de array te "vangen" die is gemaakt met de booleaanse logische bewerking en dezelfde array weer terug te sturen naar MATCH. Om dit te doen, is INDEX geconfigureerd met nul rijen en één kolom. De truc met nulrijen zorgt ervoor dat INDEX kolom 1 retourneert uit de array (die toch al één kolom is).
Waarom zou je de niet-array-versie willen? Soms vergeten mensen een matrixformule in te voeren met control + shift + enter, en retourneert de formule een onjuist resultaat. Een niet-matrixformule is dus meer "kogelvrij". De afweging is echter een complexere formule.
Opmerking: in Excel 365 is het niet nodig om matrixformules op een speciale manier in te voeren.
