Bekijk video - Zoek de tweede, de derde of de N-de overeenkomende waarde op
Als het gaat om het opzoeken van gegevens in Excel, zijn er twee geweldige functies die ik vaak gebruik: VERT.ZOEKEN en INDEX (meestal in combinatie met de MATCH-functie).
Deze formules zijn echter ontworpen om alleen het eerste exemplaar van de opzoekwaarde te vinden.
Maar wat als u de tweede, derde, vierde of de N-waarde wilt opzoeken.
Nou, het is te doen met een beetje extra werk.
In deze tutorial laat ik je op verschillende manieren (met voorbeelden) zien hoe je de tweede of de N-de waarde in Excel kunt opzoeken.
Zoek de tweede, derde of N-de waarde op in Excel
In deze zelfstudie behandel ik twee manieren om de tweede of de N-waarde in Excel op te zoeken:
- Een helperkolom gebruiken.
- Matrixformules gebruiken.
Laten we beginnen en er meteen in duiken.
Helperkolom gebruiken
Stel, je bent opleidingscoördinator in een organisatie en hebt een dataset zoals hieronder weergegeven. U wilt alle trainingen voor de naam van een werknemer plaatsen.
In de bovenstaande dataset hebben de medewerkers training gekregen over verschillende Microsoft Office-tools (Excel, PowerPoint en Word).
Nu kunt u de functie VERT.ZOEKEN of de combinatie INDEX/MATCH gebruiken om de training te vinden die een werknemer heeft voltooid. Het zal echter alleen de eerste overeenkomende instantie retourneren.
In het geval van John heeft hij bijvoorbeeld alle drie de trainingen gevolgd, maar als ik zijn naam opzoek met VERT.ZOEKEN of INDEX/MATCH, wordt altijd 'Excel' geretourneerd, wat de eerste training voor zijn naam in de lijst is .
Om dit voor elkaar te krijgen, kunnen we een hulpkolom gebruiken en daarin unieke opzoekwaarden maken.
Dit zijn de stappen:
- Voeg een kolom in vóór de kolom waarin de training wordt vermeld.
- Voer in cel B2 de volgende formule in:
=A2&AANTAL.ALS ($A$2:$A2,A2)
- Voer in cel F2 de volgende formule in en kopieer en plak voor alle andere cellen:
=IFNA(VERT.ZOEKEN($E2&KOLOMMEN($F$1:F1),$B$2:$C$14,2,0),"")
De bovenstaande formule zou de training voor elke werknemer retourneren in de volgorde waarin deze op de lijst staat. Als er geen opleiding voor een werknemer wordt vermeld, wordt er een blanco geretourneerd.
Hoe werkt deze formule?
De AANTAL.ALS-formule in de helperkolom maakt de naam van elke werknemer uniek door er een nummer aan toe te voegen. De eerste instantie van John wordt bijvoorbeeld John1, de tweede instantie wordt John2 enzovoort.
De VLOOKUP-formule gebruikt nu deze unieke werknemersnamen om de bijpassende training te vinden.
Merk op dat $E2&COLUMNS($F$1:F1) de opzoekwaarde in de formule is. Dit zou een nummer toevoegen aan de naam van de werknemer op basis van het kolomnummer. Als deze formule bijvoorbeeld in cel F2 wordt gebruikt, wordt de opzoekwaarde "John1". In cel G2 wordt het "John2" enzovoort.
Matrixformule gebruiken
Als u de oorspronkelijke gegevensset niet wilt wijzigen door hulpkolommen toe te voegen, kunt u ook een matrixformule gebruiken om de tweede, derde of de n-waarde op te zoeken.
Stel dat u dezelfde dataset heeft als hieronder weergegeven:
Hier is de formule die de juiste opzoekwaarde retourneert:
=IFERROR(INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,""),KOLOMMEN ($E$1 :E1))),"")
Kopieer deze formule en plak deze in cel E2.
Merk op dat dit een matrixformule is en dat je Control + Shift + Enter moet gebruiken (houd de Control- en Shift-toetsen ingedrukt en druk op de Enter-toets), in plaats van alleen op de Enter-toets te drukken.
Klik hier om het voorbeeldbestand te downloaden.
Hoe werkt deze formule?
Laten we deze formule in delen opsplitsen en kijken hoe het werkt.
$A$2:$A$14=$D2
Het bovenstaande deel van de formule vergelijkt elke cel in A2:A14 met de waarde in D2. In deze dataset wordt gecontroleerd of een cel de naam "Jan" bevat of niet.
Het retourneert een array van TRUE of FALSE. Als de cel de naam 'John' heeft, zou het waar zijn, anders zou het onwaar zijn.
Hieronder staat de array die u in dit voorbeeld zou krijgen:
{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
Merk op dat het TRUE heeft op de 1e, 7e en 111e positie, omdat daar de naam John in de dataset voorkomt.
IF($A$2:$A$14=$D2,RIJ($A$2:$A$14)-1,””)
De bovenstaande IF-formule gebruikt de array TRUE en FALSE en vervangt TRUE door de positie van het voorkomen in de lijst (gegeven door ROW ($ A $ 2: $ A $ 14) -1) en FALSE door "" (spaties). Het volgende is de resulterende array die u krijgt met deze IF-formule:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Opmerking dan 1, 7 en 11 zijn de plaats van voorkomen van John in de lijst.
KLEIN(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,””),KOLOMMEN ($E$1:E1))
De SMALL-functie kiest nu het eerste kleinste, tweede kleinste, derde kleinste getal uit deze array. Merk op dat het de functie KOLOMMEN gebruikt om het kolomnummer te genereren. In cel E2 retourneert de functie KOLOMMEN 1 en de functie KLEINE retourneert 1. In cel F2 retourneert de functie KOLOMMEN 2 en retourneert de functie KLEIN 7.
INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,""),KOLOMMEN ($E$1:E1) ))
De functie INDEX retourneert nu de waarde uit de lijst in kolom B op basis van de positie die wordt geretourneerd door de functie SMALL. Daarom retourneert het in cel E2 'Excel', het eerste item in B2:B14. In cel F2 wordt PowerPoint geretourneerd, het 7e item in de lijst.
Aangezien er gevallen zijn waarin er slechts één of twee trainingen zijn voor sommige werknemers, zou de INDEX-functie een fout retourneren. De functie IFERROR wordt gebruikt om een spatie terug te geven in plaats van de fout.
Merk op dat ik in deze voorbeelden bereikreferenties heb gebruikt. In praktijkvoorbeelden is het echter nuttig om de gegevens om te zetten in een Excel-tabel. Door te converteren naar een Excel-tabel, kunt u gestructureerde verwijzingen gebruiken, wat het maken van formules eenvoudiger maakt. Ook kan een Excel-tabel automatisch rekening houden met eventuele nieuwe trainingsitems die aan de lijst worden toegevoegd (zodat u de formules niet elke keer hoeft aan te passen).
Wat doe je als je de tweede, derde of N-de waarde moet opzoeken? Ik weet zeker dat er meer manieren zijn om dit te doen. Als u iets eenvoudiger gebruikt dan hier vermeld, deel het dan met ons allemaal in het opmerkingengedeelte.
Klik hier om het voorbeeldbestand te downloaden.
