Zoekopdrachten met meerdere criteria met INDEX-MATCH

De kerntruc: vermenigvuldig de twee tests van de voorwaarden binnen MATCH.

(A2:A10=G1) geeft een matrix met TRUE/FALSE voor het eerste criterium. (B2:B10=G2) doet hetzelfde voor het tweede. Door ze te vermenigvuldigen, (A2:A10=G1)*(B2:B10=G2), krijg je alleen daar 1 waar beide waarden TRUE zijn, en elders 0.

MATCH zoekt vervolgens naar de waarde 1 om de rij te vinden die aan beide voorwaarden voldoet.

In spreadsheets gedraagt TRUE zich als 1 en FALSE als 0. Door twee van deze waarden te vermenigvuldigen, boots je een logische AND na:

• 1 keer 1 = 1 (aan beide voorwaarden voldaan)
• 1 keer 0 = 0
• 0 keer 1 = 0
• 0 keer 0 = 0

Alleen rijen waarin aan beide criteria is voldaan, leveren dus een 1 op. Elke andere rij wordt 0. Die ene 1 markeert de rij die we zoeken.

Wikkel de vermenigvuldigde matrix nu in MATCH en zoek naar de exacte waarde 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) retourneert de positie van de eerste rij waarin beide voorwaarden TRUE zijn.

Als de overeenkomende combinatie in de vierde gegevensrij staat, retourneert MATCH 4. Dat is de positie die INDEX nodig heeft om het antwoord op te halen.

Geef dat MATCH-resultaat door aan INDEX voor de kolom die je daadwerkelijk wilt gebruiken, bijvoorbeeld de prijzen in C2:C10.

De volledige formule betekent: retourneer uit C2:C10 de waarde op de rij waar het product gelijk is aan G1 en de maat gelijk is aan G2.

Dit is een echte zoekopdracht met meerdere criteria, zonder hulpkolom en zonder je gegevens te herschikken.

Deze formule verwerkt matrices met voorwaarden. In moderne versies van Excel en Google Sheets druk je gewoon op Enter en werkt de formule.

In oudere versies van Excel (vóór dynamische matrices) moet je de formule bevestigen als matrixformule met Ctrl+Shift+Enter, waardoor accolades worden toegevoegd. Als je resultaat in oudere Excel niet klopt of een fout toont, ontbreekt meestal deze bevestigingsstap.

Heb je drie criteria nodig? Vermenigvuldig dan gewoon nog een test mee. Stel dat je ook een kleur in kolom D wilt vergelijken met invoer G3.

Elke extra factor (range=criterion) beperkt het resultaat verder. Alleen rijen waarin aan alle voorwaarden is voldaan, behouden een product van 1; elke FALSE maakt het volledige product 0.

Je kunt dit patroon uitbreiden naar zoveel kolommen als je nodig hebt.

Gegevens: A = product, B = maat, C = prijs. Je wilt de prijs van een "Shirt" in "Large".

• G1 = "Shirt", G2 = "Large".
• De voorwaardematrices produceren alleen op de rij met Shirt+Large een 1, bijvoorbeeld rij 4.
• MATCH(1, ..., 0) retourneert 4.
• INDEX(C2:C10, 4) retourneert de prijs op die rij.

Wijzig een van beide invoerwaarden en de formule vindt direct opnieuw de juiste rij.

Houd rekening met het volgende:

• Gelijke bereiken: elk bereik met voorwaarden en de INDEX-kolom moeten even hoog zijn.
• Geen overeenkomst: als geen enkele rij aan alle criteria voldoet, retourneert MATCH #N/A. Wikkel de volledige formule in IFERROR.
• Duplicaten: als meerdere rijen overeenkomen, retourneert MATCH alleen de eerste. Maak je criteria specifiek genoeg om één unieke rij te vinden.

Als meerdere rijen overeen kunnen komen en je hun waarden liever optelt dan één waarde ophaalt, is SUMPRODUCT een helder alternatief voor INDEX-MATCH dat als matrixformule wordt ingevoerd.

De functie vermenigvuldigt de voorwaardematrices met de kolom met waarden en telt de resultaten op. Daardoor dragen alleen rijen die aan beide criteria voldoen bij aan de uitkomst. Ctrl+Shift+Enter is niet nodig, omdat SUMPRODUCT matrices van nature verwerkt.

Gebruik INDEX-MATCH om één overeenkomende waarde op te halen en SUMPRODUCT om waarden van alle overeenkomsten samen te voegen.

Voor opzoekingen met meerdere criteria met INDEX-MATCH:

• Vermenigvuldig de voorwaardematrices met elkaar: (A=G1)*(B=G2) geeft alleen een 1 waar aan alle voorwaarden is voldaan (een logische AND).
• MATCH(1, ..., 0) zoekt de positie van die rij.
• INDEX(returnCol, position) geeft de waarde terug.

Voeg voor extra voorwaarden meer factoren van het type *(range=criterion) toe, zorg dat bereiken even hoog zijn, bevestig de formule met Ctrl+Shift+Enter in oudere versies van Excel en vang fouten af met IFERROR.