Zoeken met meerdere criteria met INDEX en MATCH

Wanneer je te maken hebt met een grote database in een Excel-werkblad met verschillende kolommen en rijkoppen, is het altijd lastig om iets te vinden dat aan meerdere criteria voldoet. In dit geval kun je een matrixformule gebruiken met de functies INDEX en MATCH.

Hoe voer je een zoekopdracht uit met meerdere criteria?

Om het product te vinden dat wit is, middelgroot en een prijs heeft van $18 zoals in de bovenstaande afbeelding, kun je gebruikmaken van de booleaanse logica om een array van 1-en en 0-en te genereren die de rijen weergeeft die aan de criteria voldoen. De functie MATCH zal dan de positie van de eerste rij vinden die aan alle criteria voldoet. Daarna zal INDEX het bijbehorende product-ID op dezelfde rij vinden.

Algemene syntaxis

=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))

√ Opmerking: Dit is een matrixformule die vereist dat je deze invoert met Ctrl + Shift + Enter.

Om het product te vinden dat wit is, middelgroot en een prijs heeft van $18, kopieer of voer de onderstaande formule in cel H8 in en druk op Ctrl + Shift + Enter om het resultaat te krijgen:

=INDEX(B5:B10,MATCH(1,("White"=C5:C10)*("Medium"=D5:D10)*(18=E5:E10),0))

Of gebruik een celverwijzing om de formule dynamisch te maken:

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

Uitleg van de formule

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

• (h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): De formule vergelijkt de kleur in de cel h3 met alle kleuren in het bereik C5:C10; vergelijkt de grootte in H5 met alle maten in D5:D10; vergelijkt de prijs in H6 met alle prijzen in E5:E10. Het initiële resultaat ziet er zo uit:
  {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
  De vermenigvuldiging zal de TRUEs en FALSEs omzetten in 1-en en 0-en:
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  Na vermenigvuldiging krijgen we een enkele array zoals deze:
  {0;0;0;0;1;0}.
• MATCH(1,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0),0) = MATCH(1,MATCH(1,{0;0;0;0;1;0},0),0): De match_type 0 vraagt de MATCH-functie om de exacte overeenkomst te vinden. De functie retourneert dan de positie van 1 in de array {0;0;0;0;1;0}, wat 5 is.
• INDEX(B5:B10B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10B5:B10,5): De INDEX-functie retourneert de 5e waarde in het product-ID-bereik B5:B10, wat 30005 is.

Gerelateerde functies

Gerelateerde formules