Zoek de dichtstbijzijnde overeenkomende waarde met meerdere criteria

In sommige gevallen moet u mogelijk de dichtstbijzijnde of benaderende overeenkomende waarde opzoeken op basis van meer dan één criterium. Met de combinatie van INDEX, MATCH en ALS functies kunt u dit snel voor elkaar krijgen in Excel.

Hoe zoekt u de dichtstbijzijnde overeenkomende waarde met meer dan één criterium?

Zoals in de onderstaande schermafbeelding te zien is, moet u de juiste persoon voor de baan vinden op basis van twee criteria: “de major is Computer” en “de werkervaring is 15 jaar”.

Opmerking: Opdat dit correct werkt, als er dubbele majors zijn, moeten de werkervaringen van deze dubbele majors in oplopende volgorde worden gesorteerd.

1. Selecteer een lege cel om het resultaat weer te geven, kopieer vervolgens onderstaande formule erin en druk op de Ctrl + Shift + Enter toetsen om het resultaat te krijgen.

=INDEX(D3:D8;MATCH(G5;ALS(B3:B8=G4;C3:C8);1))

Opmerkingen: in deze formule:

• D3:D8 is het kolombereik dat het resultaat bevat waar u naar op zoek bent;
• G5 bevat het tweede criterium (ervaringsnummer 15) waarop u de waarde gaat opzoeken;
• G4 bevat het eerste criterium (Computer) waarop u de waarde gaat opzoeken;
• B3:B8 is het bereik van cellen dat overeenkomt met het eerste criterium;
• C3:C8 is het bereik van cellen dat overeenkomt met het tweede criterium;
• Nummer 1 staat voor een benaderende zoekopdracht, wat betekent dat als de exacte waarde niet kan worden gevonden, de grootste waarde die kleiner is dan de zoekwaarde wordt gezocht;
• Deze formule moet worden ingevoerd als een matrixformule met de Ctrl + Shift + Enter toetsen.

Hoe werkt deze formule

Deze formule kan worden opgesplitst in verschillende componenten:

• ALS(B3:B8=G4;C3:C8): de ALS functie retourneert hier het resultaat als {9;13;ONWAAR;ONWAAR;ONWAAR;ONWAAR}, wat voortkomt uit het testen van de waarden in B3:B8 om te zien of ze overeenkomen met de waarde in G4. Als er een overeenkomst is, wordt de bijbehorende waarde geretourneerd, anders wordt ONWAAR geretourneerd. Hier worden twee overeenkomsten en vier niet-overeenkomsten gevonden.
• Een matrixformule =MATCH(G5;{9;13;ONWAAR;ONWAAR;ONWAAR;ONWAAR};1): de MATCH functie vindt de positie van nummer 15 (de waarde in G5) in bereik C3:C8. Aangezien nummer 15 niet kan worden gevonden, komt het overeen met de eerstvolgende kleinste waarde 13. Het resultaat hier is dus 2.
• En =INDEX(D3:D8;2): De INDEX functie retourneert de waarde van de tweede cel in bereik D3:D8. Het eindresultaat is dus Amy.

Gerelateerde functies

Gerelateerde artikelen