Haal de eerste overeenkomende waarde op in een cel ten opzichte van een lijst

Stel dat je een lijst met trefwoorden hebt, om het eerste trefwoord op te halen dat in een specifieke cel voorkomt, terwijl de cel verschillende andere waarden bevat, dan moet je een INDEX- en MATCH-formule gebruiken met behulp van de functies AGGREGATE en SEARCH.

Hoe haal je de eerste overeenkomende waarde op in een cel ten opzichte van een lijst?

Om het eerste overeenkomende trefwoord in een cel te vinden ten opzichte van de lijst met trefwoorden zoals in de bovenstaande tabel, betekent dit dat we een gedeeltelijke overeenkomst moeten uitvoeren in plaats van een exacte overeenkomst. Om dit te doen, kun je de functie SEARCH gebruiken om de posities van de trefwoorden die in de cel voorkomen als numerieke getallen door te geven aan de functie AGGREGATE. Vervolgens zal AGGREGATE het kleinste getal ophalen door zijn function_num in te stellen op 15 en het ref2-argument op 1. Gebruik vervolgens MATCH om de eerste kleinste waarde te lokaliseren en geef het positienummer door aan 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 die vereist dat je deze invoert met Ctrl + Shift + Enter.

Om het eerste overeenkomende trefwoord dat in cel B5 voorkomt ten opzichte van de kolom Trefwoorden op te halen, kopieer of voer de onderstaande formule in cel C5 in en druk op Ctrl + Shift + Enter om het resultaat te krijgen:

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat de keyword_rng in de formule niet verandert wanneer je de formule naar andere cellen verplaatst of kopieert. Er zijn echter geen dollartekens toegevoegd aan de lookup_cell omdat je wilt dat deze dynamisch blijft. Nadat je de formule hebt ingevoerd, sleep je de vulgreep naar beneden om de formule op de onderliggende cellen toe te passen.

Uitleg van de formule

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5): De functie SEARCH retourneert de positie van elk trefwoord uit het bereik $E$5:$E$7 wanneer het wordt gevonden in numerieke getallen, en de fout #WAARDE! indien niet gevonden. Het resultaat zou in een array zijn zoals deze: {15;11;#WAARDE!}.
• AGGREGATE(15,6,AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),1) = AGGREGATE(15,6,AGGREGATE(15,6,{15;11;#WAARDE!},1),1): De functie AGGREGATE met een function_num van 15 en optie van 6 retourneert de kleinste waarde in de array volgens het ref2-argument 1, terwijl foutwaarden worden genegeerd. Dus, het fragment zou 11 retourneren.
• MATCH(MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0),0) = MATCH(MATCH(11,,{15;11;#WAARDE!},0),0): De match_type 0 dwingt de functie MATCH om een exacte overeenkomst uit te voeren en retourneert de positie van 11 in de array {15;11;#WAARDE!}. Dus, de functie zal 2 retourneren.
• INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0)) = INDEX($E$5:$E$7,2): De functie INDEX retourneert vervolgens de 2e waarde in het bereik $E$5:$E$7, wat bbb is.

Opmerking

• Als er geen trefwoorden in een cel staan, wordt een foutmelding #GETAL! geretourneerd.
• De formule is niet hoofdlettergevoelig. Om een hoofdlettergevoelige overeenkomst uit te voeren, kun je eenvoudig de functie SEARCH vervangen door FIND.

Gerelateerde functies

Gerelateerde formules