In dit artikel zullen we leren hoe u de nde match in een tabel kunt opzoeken met behulp van de INDEX & MATCH-functie.
Scenario:
We moeten bijvoorbeeld de numeriek n-de overeenkomst en de bijbehorende resultaten uit de tabel vinden. We hebben een algemene formule nodig die helpt bij het vinden van de n-de match om de vereiste waarde te krijgen.
Hoe het probleem op te lossen?
Om de formule eerst te begrijpen, moeten we de volgende functies een beetje herzien:
- INDEX-functie
- MATCH-functie
Nu gaan we een formule maken met behulp van de bovenstaande functies. MATCH-functie retourneert de index van de laagste overeenkomst uit het bereik. De functie INDEX neemt de rij-index als argument en retourneert de bijbehorende vereiste resultaten. Deze functie vindt de
Generieke formule:
= INDEX ( data , MATCH ( SMALL (bereik, n), bereik, match_type ), col_num )
gegevens : array van waarden in de tabel zonder headers
bereik : lookup_array voor de laagste overeenkomst
N : nummer, nde wedstrijd
match_type : 1 (exact of op één na kleinste) of 0 (exacte overeenkomst) of -1 (exact of op één na grootste)
col_num : kolomnummer, vereiste waarde om uit de tabelkolom op te halen.
Voorbeeld:
De bovenstaande uitspraken kunnen verwarrend zijn om te begrijpen. Laten we dit dus begrijpen door de formule in een voorbeeld te gebruiken
Hier hebben we een tabel met details over het wereldcontinent. We moeten de landcode uit de gegeven tabel vinden in de opzoekwaarde als landnaam.
Benoemd bereik gebruikt voor tabel (E5:I10) & bereik (I5:I10) array.
Hier hebben we in plaats daarvan het benoemde bereik gebruikt voor celverwijzingsarray omdat het gemakkelijk te begrijpen is. We moeten zoeken naar alle details waar de prijswaarde minimaal of het laagst is in het bereik.
Gebruik de bovenstaande formule om het eerste detail te krijgen dat hoofdletter is
Gebruik de formule in de G6-cel:
= INDEX (tabel, MATCH ( KLEIN (bereik, 1) ,bereik , 0 ) , 4 )
Benoemde bereiken
tafel (E5:I10)
bereik (I5:I10)
Uitleg:
- De functie SMALL vindt de eerste laagste overeenkomst in het bereik en retourneert de waarde naar de MATCH-functie.
- MATCH-functie komt overeen met de exacte minimale waarde-overeenkomst in het prijsbereik en retourneert de rij-index naar de INDEX-functie.
- De INDEX-functie vindt de Capital-waarde met ROW-index en 4e kolom in de tabel met de naam bereik.
De formule retourneert alle resultaten voor de eerste laagste waarde. Kopieer nu de formule om de op een na kleinste of de op twee na laagste te krijgen, u hoeft alleen het n-argument onder de SMALL-functie te wijzigen om de verschillende resultaten te krijgen.
Verander nu gewoon de n-waarde en verkrijg de resultaten zoals hieronder weergegeven.
Zoals je kunt zien aan de bovenstaande momentopname, hebben we alle details verkregen die overeenkomen met de n-de waarde in de tabel. Extraheer details uit de tabel met behulp van de bovengenoemde formule.
Voorbeeld:
U kunt ook de LARGE-functie gebruiken om de n-de overeenkomst van de bovenste of de n-de hoogste overeenkomst uit het bereik te krijgen en de bijbehorende resultaten terug te geven.
Generieke formule:
= INDEX ( data , MATCH ( LARGE (bereik, n), bereik, match_type ), col_num )
gegevens : array van waarden in de tabel zonder headers
bereik : lookup_array voor de hoogste overeenkomst
N : nummer, nde wedstrijd
match_type : 1 (exact of op één na kleinste) of 0 (exacte overeenkomst) of -1 (exact of op één na grootste)
col_num : kolomnummer, vereiste waarde om uit de tabelkolom op te halen.
Voorbeeld:
De bovenstaande uitspraken kunnen verwarrend zijn om te begrijpen. Laten we dit dus begrijpen door de formule in een voorbeeld te gebruiken
Hier hebben we een tabel met details over het wereldcontinent. We moeten de landcode uit de gegeven tabel vinden in de opzoekwaarde als landnaam.
Benoemd bereik gebruikt voor tabel (E5:I10) & bereik (I5:I10) array.
Hier hebben we in plaats daarvan het benoemde bereik gebruikt voor celverwijzingsarray omdat het gemakkelijk te begrijpen is. We moeten zoeken naar alle details waar de prijswaarde maximaal of het hoogst is in het bereik.
Gebruik de bovenstaande formule om het eerste detail te krijgen dat hoofdletter is
Gebruik de formule in de G6-cel:
= INDEX (tabel, MATCH ( GROOT (bereik, 1) ,bereik , 0 ) , 4 )
Benoemde bereiken
tafel (E5:I10)
bereik (I5:I10)
Uitleg:
- De functie LARGE vindt de eerste hoogste overeenkomst in het bereik en retourneert de waarde naar de MATCH-functie.
- MATCH-functie komt overeen met de exacte n-de matchwaarde-overeenkomst in het prijsbereik en retourneert de rij-index naar de INDEX-functie.
- De INDEX-functie vindt de Capital-waarde met ROW-index en 4e kolom in de tabel met de naam bereik.
De formule retourneert alle resultaten voor de eerste laagste waarde. Kopieer nu de formule om de op een na kleinste of de op twee na laagste te krijgen, u hoeft alleen het n-argument onder de SMALL-functie te wijzigen om de verschillende resultaten te krijgen.
Verander nu gewoon de n-waarde en verkrijg de resultaten zoals hieronder weergegeven.
Zoals je kunt zien aan de bovenstaande momentopname, hebben we alle details verkregen die overeenkomen met de n-de waarde in de tabel. Extraheer details uit de tabel met behulp van de bovengenoemde formule.
Hier zijn enkele observatienotities bij het gebruik van de bovenstaande formule.
Opmerkingen:
- Waarde van n kan niet kleiner zijn dan 1 of groter zijn dan de lengte van het bereik
- De functie retourneert de fout #NA als het argument van de opzoekarray voor de functie VERGELIJKEN niet dezelfde lengte heeft als de tabelarray.
- De formule retourneert een fout als lookup_value niet overeenkomt met de waarde in de tabel lookup_array.
- De functie komt overeen met de exacte waarde, aangezien het argument voor het type overeenkomst met de functie VERGELIJKEN 0 is.
- De opzoekwaarden kunnen worden opgegeven als celverwijzing of rechtstreeks met het aanhalingsteken ( " ) in de formule als argumenten.
Ik hoop dat je hebt begrepen hoe je de nth match in de tabel kunt opzoeken met de INDEX & MATCH-functie. Bekijk hier meer artikelen over de zoekwaarde van Excel. Aarzel niet om uw vragen hieronder in het opmerkingenveld te vermelden. We zullen je zeker helpen.
Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite
Gebruik INDEX en MATCH om waarde op te zoeken : INDEX & MATCH-functie om desgewenst de waarde op te zoeken.
SUM-bereik met INDEX in Excel : Gebruik de INDEX-functie om de SUM van de waarden te vinden zoals vereist.
Hoe de INDEX-functie in Excel te gebruiken? : Zoek de INDEX van array met behulp van de INDEX-functie uitgelegd met een voorbeeld.
Hoe de MATCH-functie in Excel te gebruiken? : Zoek de MATCH in de array met behulp van de INDEX-waarde in de MATCH-functie uitgelegd met een voorbeeld.
Hoe de LOOKUP-functie in Excel te gebruiken? : Zoek de opzoekwaarde in de array met behulp van de LOOKUP-functie die met een voorbeeld wordt uitgelegd.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Zoek de opzoekwaarde in de array met behulp van de functie VERT.ZOEKEN, uitgelegd aan de hand van een voorbeeld.
populaire artikels
50 Excel-snelkoppeling om uw productiviteit te verhogen
Een vervolgkeuzelijst bewerken
Absolute referentie in Excel
Indien met voorwaardelijke opmaak
Indien met jokertekens
Opzoeken op datum
Voeg voor- en achternaam toe in Excel