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

Stel dat u een lijst trefwoorden hebt en u het eerste trefwoord wilt ophalen dat voorkomt in een specifieke cel – terwijl die cel meerdere andere waarden bevat – dan gebruikt u een INDEX- en MATCH-formule, ondersteund door de AGGREGATE- en SEARCH-functies.

Hoe haalt u de eerste overeenkomende waarde op uit een cel op basis van een lijst?

Om het eerste overeenkomende trefwoord in een cel ten opzichte van de lijst Trefwoorden op te halen, zoals in de bovenstaande tabel wordt weergegeven, voert u een ‘bevat’-overeenkomst uit in plaats van een exacte overeenkomst. Gebruik hiervoor de SEARCH-functie om de posities van de trefwoorden in de cel als numerieke waarden door te geven aan de AGGREGATE-functie. Vervolgens haalt AGGREGATE het kleinste getal op door function_num in te stellen op 15 en het ref2-argument op 1. Gebruik daarna 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 u moet invoeren met Ctrl+Shift+Enter.

Om het eerste overeenkomende trefwoord dat voorkomt in cel B5 ten opzichte van de kolom Trefwoordenop te halen, 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(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 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 deze dynamisch moet blijven. Na het invoeren van de formule 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()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5):De functie SEARCH geeft de positie van elk trefwoord uit het bereik $E$5:$E$7 als een numerieke waarde wanneer het wordt gevonden, en de fout #VALUE! als het niet wordt gevonden. Het resultaat is een matrix zoals deze: {15;11;#VALUE!}.
• AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,){15;11;#VALUE!},1):De functie AGGREGATE met een function_num van 15 en een optie van 6 retourneert de kleinste waarde in de matrix op basis van het ref2-argument 1, waarbij foutwaarden worden genegeerd. Dit fragment levert dus 11 op.
• MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=MATCH()11,{15;11;#VALUE!},0):De match_type 0 dwingt de MATCH-functie tot een exacte overeenkomst en retourneert de positie van 11 in de matrix {15;11;#VALUE!}. De functie retourneert dus 2.
• INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = INDEX($E$5:$E$7,2):De INDEX-functie retourneert vervolgens de 2de waarde in het bereik $E$5:$E$7, namelijk bbb.

Opmerking

• Als er geen trefwoorden in een cel staan, wordt een #NUM!-fout geretourneerd.
• De formule is niet hoofdlettergevoelig. Voor een hoofdlettergevoelige overeenkomst kunt u de SEARCH-functie eenvoudig vervangen door FIND.

Gerelateerde functies

Gerelateerde formules