Opzoeken met meerdere criteria met INDEX en MATCH

Als je te maken hebt met een grote database in een Excel-spreadsheet met meerdere kolommen en rijbijschriften, is het altijd lastig om iets te vinden dat aan meerdere criteria voldoet. In dit geval kunt u een matrixformule gebruiken met the INDEX en MATCH fzalven.

Hoe een zoekopdracht uitvoeren met meerdere criteria?

Om erachter te komen artikel dat is wit en Medium-formaat met een prijs van $18 zoals weergegeven in de bovenstaande afbeelding, kunt u profiteren van de booleaanse logica om een ​​array van enen en nullen te genereren om de rijen weer te geven die aan de criteria voldoen. De MATCH-functie vindt dan de positie van de eerste rij die aan alle criteria voldoet. Daarna vindt INDEX de bijbehorende product-ID op dezelfde rij.

Algemene syntaxis

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

√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter.

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

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

Of gebruik een celverwijzing om de formule dynamisch te maken:

=INDEX(B5: B10,VERGELIJKEN(1,(H4=C5: C10​H5=D5: D10​H6=E5: E10),0))

Verklaring van de formule

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

• (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): De formule vergelijkt de kleur in de cel H4 tegen alle kleuren in het assortiment C5: C10; vergelijkt de maat in H5 tegen alle maten in D5: D10; vergelijkt de prijs in H6 tegen alle prijzen in E5: E10. Het eerste resultaat is als volgt:
  {WAAR; ONWAAR; WAAR; ONWAAR; WAAR; ONWAAR}*{ONWAAR; ONWAAR; WAAR; WAAR; WAAR; ONWAAR}*{ONWAAR; ONWAAR; ONWAAR; WAAR; WAAR; ONWAAR}.
  De vermenigvuldiging transformeert de TRUEs en FALSEs naar 1s en 0s:
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  Na vermenigvuldiging hebben we een enkele array zoals deze:
  {0;0;0;0;1;0}.
• VERGELIJKEN(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = VERGELIJKEN(1,{0;0;0;0;1;0}, 0): Het match_type 0 vraagt ​​de MATCH-functie om de exacte overeenkomst te vinden. De functie retourneert dan de positie van 1 in de reeks {0;0;0;0;1;0}Dit is 5.
• INHOUDSOPGAVE(B5: B10,VERGELIJKEN(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = INDEX(B5: B10,5): De INDEX-functie retourneert de 5de waarde in het product-ID-bereik B5: B10Dit is 30005.

Gerelateerde functies

Verwante formules