Zoek de dichtstbijzijnde overeenkomstige waarde met meerdere criteria

In sommige gevallen moet u de dichtstbijzijnde of benaderende overeenkomstige waarde zoeken op basis van meerdere criteria. Met de combinatie van de functies INDEX, VERGELIJKEN en ALS voert u dit in Excel razendsnel uit.

Hoe zoekt u de dichtstbijzijnde overeenkomende waarde op basis van meerdere criteria?

Zoals in de onderstaande afbeelding wordt weergegeven, zoekt u de juiste persoon voor de functie op basis van twee criteria: „de studierichting is Computer” en „de werkervaring is 15 jaar”.

Opmerking: Om dit correct te laten werken, moeten de werkervaringen van dubbele studierichtingen in oplopende volgorde zijn gesorteerd.

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

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

Opmerkingen:bij deze formule:

• D3:D8is het kolomgebied dat het resultaat bevat waarnaar u zoekt;
• G5bevat het tweede criterium (ervaringsgetal 15), waarop u de opzoekwaarde baseert;
• G4bevat het eerste criterium (Computer), waarop u de opzoekwaarde baseert;
• B3:B8is het celbereik dat overeenkomt met het eerste criterium;
• C3:C8is het celbereik dat overeenkomt met het tweede criterium;
• Getal 1is een benaderende zoekopdracht, wat betekent dat als de exacte waarde niet kan worden gevonden, de grootste waarde wordt gezocht die kleiner is dan de opzoekwaarde;
• Deze formule moet als matrixformule worden ingevoerd met de toetsencombinatie Ctrl+Shift+Enter.

Hoe deze formule werkt

Deze formule kan worden opgedeeld in verschillende onderdelen:

• IF(B3:B8=G4,C3:C8): de ALS-functie retourneert hier het resultaat {9;13;FALSE;FALSE;FALSE;FALSE}, verkregen door de waarden in B3:B8 te vergelijken met de waarde in G4. Als er een overeenkomst is, wordt de bijbehorende waarde uit C3:C8 geretourneerd; anders FALSE. In dit geval zijn er twee overeenkomsten en vier afwijkingen.
• Een matrixformule =VERGELIJKEN(G5;{9;13;ONWAAR;ONWAAR;ONWAAR;ONWAAR};1): de VERGELIJKEN-functie zoekt de positie van het getal 15 (de waarde in G5) in het bereik C3:C8. Omdat 15 niet voorkomt, wordt de grootste waarde die kleiner is dan 15 gebruikt—namelijk 13. Het resultaat is daarom 2.
• En =INDEX(D3:D8,2): de INDEX-functie retourneert de waarde van de tweede cel in het bereik D3:D8. Het eindresultaat is dus Amy.

Gerelateerde functies

Gerelateerde artikelen