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.

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): Dé 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

Verwante formules