Haal de eerste overeenkomende waarde in cel op tegen een lijst
Stel dat u een lijst met trefwoorden hebt, om het eerste trefwoord op te halen dat in een specifieke cel verscheen, terwijl de cel verschillende andere waarden bevat, moet u een INDEX en MATCH formule met behulp van AGGREGAAT en ZOEKEN functies.
Hoe de eerste overeenkomende waarde in de cel op te halen tegen een lijst?
Om het te krijgen eerste overeenkomende trefwoord in een cel tegen de lijst met trefwoorden zoals in de bovenstaande tabel, betekent dit dat we een 'contain match' moeten uitvoeren in plaats van een exacte match. Om dit te doen, kunt u de functie ZOEKEN gebruiken om de posities van de trefwoorden die in de cel in numerieke getallen zijn verschenen, in de AGGREGATE-functie in te voeren. Dan krijgt AGGREGATE het kleinste getal door zijn . in te stellen function_num tot 15 en het argument ref2 tot 1. Gebruik vervolgens MATCH om de eerste kleinste waarde te vinden en voer het positienummer in bij INDEX om de waarde op die positie op te halen.
Algemene syntaxis
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter.
- trefwoord_rng: Het bereik van cellen met trefwoorden.
- opzoeken_cel: De cel die moet worden opgezocht als deze de trefwoorden bevat.
Om de . op te halen eerste overeenkomende trefwoord verscheen in cel B5 tegen de kolom Trefwoorden, kopieer of voer de onderstaande formule in de cel C5 in en druk op Ctrl + Shift + Enter om het resultaat te krijgen:
=INDEX($ E $ 5: $ E $ 7,OVEREENKOMST(AGGREGAAT(15,6;ZOEKEN($ E $ 5: $ E $ 7,B5),1),ZOEKEN($ E $ 5: $ E $ 7,B5),0))
√ Opmerking: de dollartekens ($) hierboven geven absolute referenties aan, wat betekent dat de trefwoord_rng in de formule verandert niet wanneer u de formule naar andere cellen verplaatst of kopieert. Er zijn echter geen dollartekens toegevoegd aan de opzoeken_cel omdat je wilt dat het dynamisch is. Sleep na het invoeren van de formule de vulgreep naar beneden om de formule op de onderstaande cellen toe te passen.
Verklaring van de formule
=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- ZOEKEN ($E$5:$E$7,B5): De functie ZOEKEN retourneert de positie van elk trefwoord uit het bereik $ E $ 5: $ E $ 7 wanneer gevonden in numerieke getallen, en de #WAARDE! fout indien niet gevonden. Het resultaat zou zijn in een array als deze: {15;11;#WAARDE!}.
- AGGREGAAT(15,6,ZOEKEN ($E$5:$E$7,B5), 1) = AGGREGAAT(15,6,{15;11;#WAARDE!}, 1): De AGGREGATE-functie met a functie_getal van 15 en optie van 6 retourneert de kleinste waarde in de array volgens de ref2-argument 1, terwijl foutwaarden worden genegeerd. Dus het fragment zou terugkeren 11.
- BIJ ELKAAR PASSEN(AGGREGAAT(15,6,ZOEKEN ($E$5:$E$7,B5), 1),ZOEKEN ($E$5:$E$7,B5), 0) = BIJ ELKAAR PASSEN(11,{15;11;#WAARDE!}, 0): De match_type 0 dwingt de MATCH-functie om een exacte match uit te voeren en de positie van te retourneren 11 in de reeks {15;11;#WAARDE!}. Dus de functie zal terugkeren 2.
- INDEX($E$5:$E$7,BIJ ELKAAR PASSEN(AGGREGAAT(15,6,ZOEKEN ($E$5:$E$7,B5), 1),ZOEKEN ($E$5:$E$7,B5), 0)) = INDEX($E$5:$E$7,2): De INDEX-functie retourneert dan de 2nd waarde in het bereik $ E $ 5: $ E $ 7Dit is bbb.
Opmerking
- Als er geen trefwoorden in een cel staan, a #GETAL! fout zal worden geretourneerd.
- De formule is niet hoofdlettergevoelig. Om een hoofdlettergevoelige overeenkomst uit te voeren, kunt u de SEARCH-functie eenvoudig vervangen door: VINDEN.
Gerelateerde functies
De Excel INDEX-functie retourneert de weergegeven waarde op basis van een bepaalde positie uit een bereik of een matrix.
De Excel MATCH-functie zoekt naar een specifieke waarde in een celbereik en retourneert de relatieve positie van de waarde.
In Excel kan de SEARCH-functie u helpen om de positie van een specifiek teken of substring uit de gegeven tekstreeks te vinden, zoals in het volgende screenshot. In deze tutorial zal ik introduceren hoe je deze SEARCH-functie in Excel kunt gebruiken.
De Excel AGGREGATE-functie retourneert een totaal van berekeningen zoals SUM, COUNT, SMALLL enzovoort met de optie om fouten en verborgen rijen te negeren.
Verwante formules
Haal de eerste lijstwaarde op uit een cel
Om het eerste trefwoord dat in een bereik wordt weergegeven uit een specifieke cel op te halen, terwijl de cel een van de verschillende waarden bevat, moet u een nogal complexe matrixformule gebruiken met de functies INDEX, MATCH, ISNUMBER en SEARCH.
Exacte match met INDEX en MATCH
Als u de in Excel vermelde informatie over een specifiek product, film of persoon, enz. wilt achterhalen, moet u goed gebruik maken van de combinatie van INDEX- en MATCH-functies.
Controleer of een cel een specifieke tekst bevat
Hier biedt de tutorial enkele formules om te controleren of een cel een specifieke tekst bevat en TRUE en FALSE te retourneren zoals onderstaand screenshot, en legt de argumenten uit en hoe de formules werken.
Controleer of cel al veel dingen bevat
Stel dat er in Excel een lijst met waarden is in kolom E, dan wilt u controleren of de cellen in kolom B alle waarden in kolom E bevatten en TRUE of FALSE retourneren zoals onderstaand screenshot. Hier in deze tutorial biedt het een formule om deze taak op te lossen.
Controleer of cel een van de vele dingen bevat
Deze zelfstudie biedt een formule om te controleren of een cel een van meerdere waarden in Excel bevat, en legt de argumenten in de formule uit en hoe de formule werkt.
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...
Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)
- Een seconde om te schakelen tussen tientallen geopende documenten!
- Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.
