Index komt overeen met meerdere criteria rijen en kolommen
We gebruiken allemaal VERT.ZOEKEN dag in dag uit 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 kunt gebruiken om meerdere criteria voor rijen en kolommen te matchen met voorbeelden.
Voorbeeld # 1 - INDEX + MATCH-formule
Niet de meeste Excel-gebruikers zoeken functies op die verder gaan dan VERT.ZOEKEN, er kunnen zoveel redenen zijn. Laten we in ieder geval een eenvoudige inleiding tot deze formule hebben voordat we naar het gevorderde niveau gaan.
Bekijk bijvoorbeeld de onderstaande datastructuur in Excel.
We hebben "Sales Rep" -namen 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 + VERGELIJKEN gebruiken.
INDEX zoekt naar de genoemde rijnummerwaarde in het bereik A2: A11, en in dit bereik moeten we aangeven uit welke rij we de verkoopwaarde nodig hebben. Deze rijwaarde is gebaseerd op de naam "Verkoopvertegenwoordiger" die is geselecteerd in de vervolgkeuzelijst in Excel, dus de functie VERGELIJKEN 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 'Verkoopvertegenwoordiger'. Van deze tabel hebben we dynamische resultaten nodig, zoals in cel A15, ik heb een vervolgkeuzelijst "Verkoopvertegenwoordiger" gemaakt en in cel B14 een vervolgkeuzelijst "Maand".
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 'april' 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: WAARDE ZOEKEN van de functie VERGELIJKEN 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 waarvan 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 opzoekwaarde voor tijd 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 "Exact Match" als criterium. Sluit twee haakjes en druk op de enter-toets om het resultaat te krijgen.
Zoals we hierboven kunnen zien, hebben we "Rep 6" en "Apr" als de maand gekozen, en onze formule heeft de verkoopwaarde voor de maand "Apr" voor "Rep 6" geretourneerd.
Opmerking: een geelgekleurde cel is de referentie voor jou.
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.
