Zoek de meest overeenkomende waarde op met meerdere criteria
In sommige gevallen moet u op basis van meer dan één criterium mogelijk de overeenkomende of geschatte waarde opzoeken. Met de combinatie van INDEX, MATCH en IF functies, kunt u dit snel in Excel doen.
Hoe de meest overeenkomende waarde met meer dan één criterium opzoeken?
Zoals de onderstaande schermafbeelding laat zien, moet u de juiste persoon voor de baan vinden op basis van twee criteria “de hoofdvak is Computer”En“ de werkervaring is 15 jaar ”.
Note: Om dit correct te laten werken, 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 uit te voeren, kopieer de onderstaande formule erin en druk op Ctrl + Shift + Enter toetsen om het resultaat te krijgen.
=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))
Opmerkingen: in deze formule:
- D3: D8 is het kolombereik dat het resultaat bevat waarnaar u zoekt;
- G5 bevat het tweede criterium (ervaringsnummer 15) waarop u waarde opzoekt op basis van;
- G4 bevat de eerste criteria (computer) waarop u de waarde zoekt op basis van;
- 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;
- Telefoon Nummer 1 is een geschatte opzoekfunctie, wat betekent dat als de exacte waarde niet kan worden gevonden, de grootste waarde wordt gevonden die kleiner is dan de opzoekwaarde;
- Deze formule moet worden ingevoerd als een matrixformule met de Ctrl + Shift + Enter sleutels.
Hoe deze formule werkt
Deze formule kan worden onderverdeeld in verschillende componenten:
- IF(B3:B8=G4,C3:C8): de ALS-functie retourneert hier het resultaat als {9;13;FALSE;FALSE;FALSE;FALSE}, die afkomstig is van 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 overeenkomstige waarde geretourneerd, anders wordt FALSE geretourneerd. Hier vindt u twee overeenkomsten en vier mismatches.
- Een matrixformule =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): de MATCH-functie vindt de positie van nummer 15 (de waarde in G5) in bereik C3: C8. Omdat het getal 15 niet kan worden gevonden, komt het overeen met de volgende kleinste waarde 13. Dus het resultaat is hier 2.
- En =INDEX(D3:D8,2): De functie INDEX retourneert de waarde van de tweede cel in het bereik D3: D8. Dus het uiteindelijke resultaat is Amy.
Gerelateerde functies
Excel ALS-functie
De ALS-functie is een van de eenvoudigste en handigste functies in de Excel-werkmap. Het voert een eenvoudige logische test uit die afhankelijk is van het vergelijkingsresultaat, en het retourneert één waarde als een resultaat WAAR is, of een andere waarde als het resultaat FALSE is.
Excel MATCH-functie
De Microsoft Excel MATCH-functie zoekt naar een specifieke waarde in een celbereik en retourneert de relatieve positie van deze waarde.
Excel INDEX-functie
De functie INDEX retourneert de weergegeven waarde op basis van een bepaalde positie uit een bereik of een array.
Gerelateerde artikelen
Gemiddelde cellen op basis van meerdere criteria
In Excel zijn de meesten van ons misschien bekend met AANTAL.ALS en SOM.ALS-functies, ze kunnen ons helpen waarden te tellen of op te tellen op basis van criteria. Maar heb je ooit geprobeerd om het gemiddelde van waarden te berekenen op basis van een of meer criteria in Excel? Deze tutorial biedt voorbeelden en formules in details om het gemakkelijk voor elkaar te krijgen.
Klik om meer te weten ...
Tel cellen als aan een van meerdere criteria is voldaan
Deze tutorial deelt de manieren om cellen te tellen als ze X of Y of Z… etc. in Excel bevatten.
Klik om meer te weten ...
Tel unieke waarden op basis van meerdere criteria
In dit artikel worden enkele voorbeelden gegeven om unieke waarden stap voor stap te tellen op basis van een of meer criteria in een werkblad met gedetailleerde methoden.
Klik om meer te weten ...
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...
Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)
- Een seconde om te schakelen tussen tientallen geopende documenten!
- Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.