Eerste lijstwaarde ophalen uit een cel

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

Hoe haalt u de eerste waarde uit een lijst in een cel?

Om het eerste overeenkomende trefwoord op te halen dat in de cel voorkomt ten opzichte van de kolom Trefwoorden, zoals hierboven wordt weergegeven, moet u een gedeeltelijke overeenkomst gebruiken in plaats van een exacte overeenkomst. Combineer hiervoor de ISGETAL- en ZOEKEN-functies om een WAAR/ONWAAR-matrix te krijgen. Gebruik vervolgens VERGELIJKEN om de eerste WAAR-waarde te vinden en geef het bijbehorende positienummer door aan INDEX om de waarde op die positie op te halen.

Algemene syntaxis

=INDEX()keyword_rng,MATCH(TRUE,ISNUMBER(SEARCH()))keyword_rng,lookup_cell)),0))

√ Opmerking: Dit is een matrixformule die u moet invoeren met Ctrl+Shift+Enter.

Om de eerste overeenkomst uit de trefwoordenlijst op te halen uit cel B5, kopieert of typt u de onderstaande formule in cel C5 en drukt u op Ctrl+Shift+Enterom het resultaat te verkrijgen:

=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 keyword_rngin de formule niet verandert wanneer u de formule naar andere cellen verplaatst of kopieert. Er zijn echter geen dollartekens toegevoegd aan de lookup_cellomdat u deze dynamisch wilt houden. Nadat u de formule hebt ingevoerd, sleept u de vulgreep omlaag om de formule op de cellen eronder toe te passen.

Uitleg van de formule

=INDEX(($E$5:$E$7,))MATCH(TRUE,)ISNUMBER()SEARCH($E$5:$E$7,B5)),0))

• SEARCH($E$5:$E$7,B5):De functie SEARCH geeft de positie van de trefwoorden uit het bereik $E$5:$E$7 als deze worden gevonden, weergegeven als getallen, en de foutmelding #VALUE! als ze niet worden gevonden. Het resultaat is een matrix zoals deze: {15;11;#VALUE!}.
• ISNUMBER()SEARCH($E$5:$E$7,B5))=ISNUMBER(){15;11;#VALUE!}):De functie ISNUMBER zet de getallen in de matrix om naar WAAR-waarden en de #VALUE!-fouten naar ONWAAR-waarden, zoals hier:{WAAR;WAAR;ONWAAR}.
• MATCH(WAAR,)ISNUMBER()SEARCH($E$5:$E$7,B5)),0)=MATCH(WAAR,){WAAR;WAAR;ONWAAR},0):Door de match_type 0 dwingt u de MATCH-functie tot een exacte overeenkomst en retourneert deze de positie van de eerste WAAR-waarde in de matrix {WAAR;WAAR;ONWAAR}. De functie levert dus 1.
• INDEX(($E$5:$E$7,))MATCH(WAAR,)ISNUMBER()SEARCH($E$5:$E$7,B5)),0)) = INDEX(($E$5:$E$7,)1):De INDEX-functie retourneert vervolgens de 1e waarde in het bereik $E$5:$E$7, namelijk AAA.

Opmerking

• Als een cel twee of meer trefwoorden bevat, retourneert de formule het eerste trefwoord uit het opgegeven trefwoordenbereik.
• De formule is niet hoofdlettergevoelig. Voor een hoofdlettergevoelige zoekopdracht vervangt u de SEARCH-functie eenvoudig door FIND.
• U kunt de lijst met trefwoorden ook vervangen door een vaste tekenreeks als matrixconstante (bijv. {„AAA"}) als u alleen wilt controleren of de cellen deze tekenreeks bevatten.

Gerelateerde functies

Gerelateerde formules