Haal de eerste lijstwaarde op uit een cel

Om het eerste trefwoord dat in een bereik wordt vermeld, op te halen uit een specifieke cel terwijl de cel één van meerdere waarden bevat, moet je een vrij complexe matrixformule gebruiken met de functies INDEX, MATCH, ISNUMBER en SEARCH.

Hoe haal je de eerste lijstwaarde op uit een cel?

Om het eerste overeenkomende trefwoord te krijgen dat in de cellen voorkomt ten opzichte van de kolom Trefwoorden zoals hierboven weergegeven, betekent dit dat we een gedeeltelijke overeenkomst moeten uitvoeren in plaats van een exacte overeenkomst. Om dit te doen, kun je de functies ISNUMBER en SEARCH combineren om een array van WAAR en ONWAAR te krijgen. Gebruik vervolgens MATCH om de eerste WAAR-waarde te vinden en geef het positienummer door aan INDEX om de waarde op die positie op te halen.

Algemene syntaxis

=INDEX(trefwoord_bereik,MATCH(WAAR,ISNUMBER(SEARCH(trefwoord_bereik,zoekcel)),0))

√ Opmerking: Dit is een matrixformule die vereist dat je deze invoert met Ctrl + Shift + Enter.

Om de eerste overeenkomst te vinden in de trefwoordlijst uit cel B5, 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(WAAR;ISNUMBER(SEARCH(($E$5:$E$7;B5));0))

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

Uitleg van de formule

=INDEX(($E$5:$E$7;MATCH(WAAR;ISNUMBER(SEARCH($E$5:$E$7;B5));0))

• SEARCH($E$5:$E$7;B5): De functie SEARCH retourneert de positie van de trefwoorden uit het bereik $E$5:$E$7 wanneer ze worden gevonden in numerieke getallen, en de fout #WAARDE! indien niet gevonden. Het resultaat zou in een array zijn zoals deze: {15;11;#WAARDE!}.
• ISNUMBER(ISNUMBER(SEARCH($E$5:$E$7;B5))) = ISNUMBER(ISNUMBER({15;11;#WAARDE!})): De functie ISNUMBER zal de getallen in de array omzetten in WAARs en #WAARDE! waarden naar ONWAARs zoals deze: {WAAR;WAAR;ONWAAR}.
• MATCH(WAAR;ISNUMBER(SEARCH($E$5:$E$7;B5));0) = MATCH(WAAR;{WAAR;WAAR;ONWAAR};0): Het match_type 0 dwingt de functie MATCH om een exacte overeenkomst uit te voeren en de positie van de eerste WAAR in de array {WAAR;WAAR;ONWAAR} terug te geven. Dus zal de functie 1 retourneren.
• INDEX(($E$5:$E$7;MATCH(WAAR;ISNUMBER(SEARCH($E$5:$E$7;B5));0)) = INDEX(($E$5:$E$7;1): De functie INDEX retourneert vervolgens de 1e waarde in het bereik $E$5:$E$7, wat AAA is.

Opmerking

• Als een cel twee of meer trefwoorden bevat, zal de formule het eerste trefwoord retourneren dat in het trefwoordbereik wordt vermeld.
• De formule is niet hoofdlettergevoelig. Om een hoofdlettergevoelige overeenkomst uit te voeren, kun je eenvoudig de functie SEARCH vervangen door FIND.
• Je kunt ook de trefwoordlijst vervangen door een hardcoded string als arrayconstante (bijv. {“AAA”}) als je alleen wilt controleren of de cellen deze string bevatten.

Gerelateerde functies

Gerelateerde formules