Met VERT.ZOEKEN krijgen we altijd de eerste match. Hetzelfde gebeurt met de functie INDEX MATCH. Dus hoe kunnen we de tweede wedstrijd of de 3e of de nth VERT.ZOEKEN? In dit artikel zullen we leren hoe we het N-de voorkomen van een waarde binnen bereik kunnen krijgen.
Generieke formule
{=KLEIN(ALS(bereik = waarde,RIJ(bereik)-RIJ(first_cell_in_range)+1),N)}
Let op: dit is een matrix formule. Je moet het invoeren met CTRL + SHIFT + ENTER.
Bereik: het bereik waarin u wilt zoeken Nde positie van waarde.
Waarde: de waarde die u zoekt Ne positie in debereik.
Eerste_cel_in_bereik: de eerste cel in debereik. Als het bereik A2:A10 is, is de eerste cel in het bereik A2.
N: de voorval aantal waarden.
Laten we een voorbeeld bekijken om dingen duidelijk te maken.
Voorbeeld: Zoek de tweede overeenkomst in Excel
Dus hier heb ik deze lijst met namen in Excel-bereik A2: A10. Ik heb dit bereik genoemd als: namen. Nu wil ik de positie krijgen van het tweede voorkomen van "Rony" in namen.
In de bovenstaande afbeelding kunnen we zien dat het op de 7e positie staat in het bereik A2:A10 (namen). Nu moeten we zijn positie bepalen met behulp van een Excel-formule.
Pas de bovenstaande generieke formule in C2 toe om het tweede exemplaar van Rony in de lijst op te zoeken.
{=KLEIN(ALS(namen = "Ronie" ,RIJ(namen)-RIJ(A2)+1),2)}
Voer het in met CTRL + SHIFT + ENTER …
En we hebben een antwoord. Er wordt 7 weergegeven, wat correct is. Als u de waarde van n in 3 verandert, krijgt u 8. Als u de waarde van n groter maakt dan het voorkomen van de waarde in het bereik, wordt de fout #GETAL geretourneerd.
Hoe werkt het?
Nou, het is vrij eenvoudig. Laten we elk deel een voor een bekijken.
INDIEN(namen = "Ronie" ,RIJ(namen)-RIJ(A2)+1) :
In IF retourneert name=“Rony” een array van TRUE en FALSE. TRUE wanneer een cel binnen bereik is namen (A2:A10) komt overeen met "Rony".{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
Volgende RIJ(namen)-RIJ(A2)+1:
RIJ(namen): hier retourneert de ROW-functie het rijnummer van elke cel in namen. {2;3;4;5;6;7;8;9;10}.
RIJ(namen)-RIJ(A2)Vervolgens trekken we rijnummer van A2 af van elke waarde in de gegeven array. Dit geeft ons een reeks serienummers vanaf 0. {0;1;2;3;4;5;6;7;8}.
RIJ(namen)-RIJ(A2)+1: Om serienummers vanaf 1 te krijgen, voegen we 1 toe aan elke waarde in deze array. Dit geeft ons het serienummer vanaf 1. {1;2;3;4;5;6;7;8;9}.
Nu hebben we ALS({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE},{1;2;3;4;5;6;7;8;9}). Dit wordt opgelost in {1;FALSE;FALSE;FALSE;FALSE;FALSE;7;8;FALSE}.
Nu hebben we de formule opgelost tot SMALL({1;FALSE;FALSE;FALSE;FALSE;FALSE;7;8;FALSE},2). Nu retourneert SMALL de op een na kleinste waarde in het bereik, namelijk 7.
Hoe gebruiken we het?
De vraag komt op: wat is het voordeel van het krijgen van een onbewerkte index van de n-de wedstrijd? Het zou nuttiger zijn als u gerelateerde informatie uit de n-de waarde zou kunnen halen. Nou, dat kan ook. Als we waarde willen krijgen van de waarde van de aangrenzende cel van n-de overeenkomst in bereik namen (A2:A10).
{=INDEX(B2:B10, KLEIN(ALS(namen = "Ronie" ,RIJ(namen)-RIJ(A2)+1),2))}
Dus ja jongens, dit is hoe je de nde wedstrijd in een reeks kunt krijgen. Ik hoop dat ik voldoende uitleg heb gegeven. Als je twijfels hebt over dit artikel of een ander Excel/VBA-gerelateerd onderwerp, schrijf dan in de comments hieronder.
Hoe u een opeenvolgend rijnummer in Excel kunt krijgen
Vlookup Top 5-waarden met dubbele waarden met INDEX-MATCH in Excel
VERT.ZOEKEN Meerdere waarden
Gebruik INDEX en MATCH om waarde op te zoeken
Opzoekwaarde met meerdere criteria
Populaire artikels:
De VERT.ZOEKEN-functie in Excel
AANTAL.ALS in Excel 2016
Hoe de SUMIF-functie in Excel te gebruiken?