Index komt overeen met meerdere criteria rijen en kolommen
We gebruiken allemaal dag in dag uit VERT.ZOEKEN om de gegevens op te halen en we zijn ons ook bewust van het feit dat VERT.ZOEKEN de gegevens van links naar rechts kan ophalen, dus de opzoekwaarde moet altijd aan de linkerkant van de resultaatkolommen staan. We hebben echter verschillende alternatieven die kunnen worden gebruikt als alternatief voor de functie VERT.ZOEKEN in Excel. Met geavanceerde technologie kunnen we deze INDEX + MATCH-formule gebruiken om aan meerdere criteria voor rijen en kolommen te voldoen. Dus dit speciale artikel zal u in detail over deze techniek leiden.
Hoe de INDEX + MATCH-formule te gebruiken om aan meerdere criteria te voldoen?
Hier leggen we uit hoe u de index + match-formule gebruikt om meerdere criteria voor rijen en kolommen te matchen met voorbeelden.
U kunt deze Excel-sjabloon voor indexafstemming met meerdere criteria hier downloaden - Excel-sjabloon voor indexafstemming met meerdere criteriaVoorbeeld # 1 - INDEX + MATCH-formule
Niet de meerderheid van de Excel-gebruikers zoekt functies op buiten VERT.ZOEKEN, er kunnen zoveel redenen zijn. Laten we in ieder geval een eenvoudige inleiding hebben op deze formule voordat we naar een gevorderd niveau gaan.
Bekijk bijvoorbeeld de onderstaande datastructuur in Excel.
We hebben namen van "Sales Rep" en hun respectievelijke verkoopwaarden. Aan de andere kant hebben we een vervolgkeuzelijst met "Verkoopvertegenwoordiger" in cel D2.
Op basis van de selectie die we maken uit de vervolgkeuzelijst, moet het verkoopbedrag in cel E2 verschijnen.
Het probleem is dat we de VERT.ZOEKEN-formule niet kunnen toepassen omdat de opzoekwaarde "Verkoopvertegenwoordiger" rechts van de resultaatkolom "Verkoop" staat, dus in deze gevallen kunnen we de combinatie-opzoekwaardeformule INDEX + MATCH gebruiken.
INDEX zoekt naar de genoemde rijnummerwaarde in het bereik A2: A11 en in dit bereik moeten we opgeven uit welke rij we de verkoopwaarde moeten halen. Deze rijwaarde is gebaseerd op de naam "Verkoopvertegenwoordiger" die is geselecteerd in de vervolgkeuzelijst in Excel, dus de functie MATCH zoekt naar het rijnummer "Verkoopvertegenwoordiger" in het bereik B2: B11 en retourneert het rijnummer van de overeenkomende waarde .
Voorbeeld # 2 - Meerdere criteria in INDEX + MATCH-formule
Nu hebben we een datastructuur zoals hieronder.
We hebben maandelijkse verkoopwaarden van "Sales Rep". Van deze tabel hebben we dynamische resultaten nodig, zoals in cel A15. Ik heb een vervolgkeuzelijst "Verkoopvertegenwoordiger" gemaakt en in cel B14 heb ik een vervolgkeuzelijst "Maand" gemaakt.
Op basis van de selectie die in deze twee cellen is gemaakt, moet onze formule de gegevens uit de bovenstaande tabel ophalen.
Als ik bijvoorbeeld "Rep 8" en "Apr" kies, moet de verkoopwaarde van "Rep 8" voor de maand "Apr" worden weergegeven.
In deze gevallen moeten we dus zowel rijen als kolommen matchen. Volg de onderstaande stappen om de formule toe te passen zodat deze overeenkomt met zowel rijen als kolommen.
Stap 1: Open de INDEX-functie in cel B15.
Stap 2: Het eerste argument van de INDEX-functie is "Array", dwz uit welk celbereik we het resultaat nodig hebben. In dit geval hebben we dus verkoopwaarden nodig, dus kies het celbereik van B2 tot G11.
Stap 3: Volgend argument van de INDEX-functie uit welke rij van het geselecteerde bereik we het resultaat nodig hebben. In dit geval moeten we aankomen bij het rijnummer "Verkoopvertegenwoordiger" op basis van de selectie gemaakt in de cel A15 vervolgkeuzelijst. Dus om het rijnummer dynamisch op te halen op basis van de selectie open MATCH-functie.
Stap 4: ZOEKWAARDE van de MATCH-functie is "Verkoopvertegenwoordiger", dus kies cel A15 als referentie.
Stap 5: De opzoekmatrix zal in de hoofdtabel de naamreeks "Verkoopvertegenwoordiger" zijn. Kies dus bereik als A2 tot A11.
Stap 6: Match Type of MATCH-functie zal exact zijn, dus voer nul in als de argumentwaarde.
Stap 7: Het volgende argument van de INDEX-functie is “Kolomnummer”, dwz uit het geselecteerde celbereik waaruit we het resultaat nodig hebben. Dit is afhankelijk van de maand die we selecteren in de vervolgkeuzelijst van cel B14. Dus om het kolomnummer automatisch te krijgen, opent u een andere MATCH-functie.
Stap 8: Deze tijdopzoekwaarde is de naam van de maand, dus selecteer cel B14 als referentie.
Stap 9: De opzoekmatrix is het maandbereik van cellen in de hoofdtabel, dwz van B1 tot G1.
Stap 10: Het laatste argument is het zoektype, kies "Exacte overeenkomst" als criterium. Sluit twee haakjes en druk op de Enter-toets om het resultaat te krijgen.
Zoals we hierboven kunnen zien, hebben we gekozen voor "Rep 6" en "Apr" als de maand en onze formule heeft de verkoopwaarde voor de maand "Apr" voor "Rep 6" geretourneerd.
Opmerking: geelgekleurde cel is de referentie voor u.
Dingen om te onthouden
- Een combinatie van INDEX + MATCH kan krachtiger zijn dan de VERT.ZOEKEN-formule.
- INDEX & MATCH kunnen zowel rijen als kolomkoppen matchen en het resultaat van de middelste tabel retourneren.
- VERGELIJKEN kan het rijnummer en kolomnummer van de tabelkoppen van zowel rijen als kolommen retourneren.
