Generieke formule
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Samenvatting
Als u een cel op een van verschillende dingen wilt controleren en de eerste overeenkomst in de lijst wilt retourneren, kunt u een INDEX / MATCH-formule gebruiken die SEARCH of FIND gebruikt om een overeenkomst te zoeken. In het getoonde voorbeeld is de formule in C5:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
waarbij "dingen" het benoemde bereik E5: E9 is.
Opmerking: dit is een matrixformule en moet worden ingevoerd met Control + Shift + Enter.
Uitleg
In dit voorbeeld hebben we een lijst met kleuren in een benoemd bereik met de naam "dingen" (E5: E9). We willen de tekst in kolom B controleren om te zien of deze een van deze kleuren bevat. Dan willen we de naam van de eerste gevonden kleur teruggeven.
Deze formule werkt van binnen naar buiten en gebruikt de ISNUMBER-functie en de SEARCH-functie om de tekst in B5 te doorzoeken voor elke kleur vermeld in "dingen" zoals deze:
ISNUMBER(SEARCH(things,B5)
Deze uitdrukking is gebaseerd op een formule (hier in detail uitgelegd) die een cel controleert op een enkele subtekenreeks. Als de cel de subtekenreeks bevat, retourneert de uitdrukking WAAR. Als dit niet het geval is, retourneert de uitdrukking ONWAAR.
Als we deze SEARCH een lijst met dingen geven (in plaats van één ding), krijgen we een reeks resultaten terug. Elke gevonden kleur genereert een numerieke positie en kleuren die niet worden gevonden, genereren een fout:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
De ISNUMBER-functie converteert vervolgens resultaten naar TRUE / FALSE-waarden. Elk getal wordt WAAR, en elke fout (niet gevonden) wordt ONWAAR. Het resultaat is een array als deze:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Deze array wordt teruggestuurd naar de MATCH-functie als het array-argument. De opzoekwaarde is WAAR en het zoektype wordt op nul gezet om een exacte overeenkomst af te dwingen. Als er een overeenkomende kleur is, geeft VERGELIJKEN de positie als eerste TRUE gevonden terug. Deze waarde wordt in de INDEX-functie ingevoerd als het rijnummer, met het benoemde bereik "dingen" als de array. Als er minstens één overeenkomst is, retourneert INDEX de kleur op die positie. Als er geen overeenkomst wordt gevonden, retourneert deze formule de fout # N / B.
Met hardgecodeerde waarden
Als u in dit voorbeeld geen extern benoemd bereik zoals "dingen" wilt instellen, kunt u waarden als volgt hard coderen in de formule als "matrixconstanten":
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Krijg de eerste match in cel
De taal hier is nogal verwarrend, maar de bovenstaande formule retourneert de eerste overeenkomst die wordt gevonden in de lijst met dingen waarnaar moet worden gezocht. Als u in plaats daarvan de eerste overeenkomst wilt retourneren die is gevonden in de cel die wordt getest, kunt u een formule als deze proberen:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
In deze versie van de formule is de MATCH-functie ingesteld om naar het resultaat van dit fragment te zoeken:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
die de functie AGGREGATE gebruikt om de minimumwaarde op te halen in de resultaten die worden geretourneerd door SEARCH. We hebben hier AGGREGATE nodig, omdat de inkomende array waarschijnlijk fouten bevat (geretourneerd door SEARCH wanneer dingen niet worden gevonden), en we hebben een functie nodig die deze fouten negeert en ons toch de minimale numerieke waarde geeft.
Het resultaat van AGGREGATE wordt direct naar MATCH geretourneerd als de opzoekwaarde, samen met dezelfde array die wordt geretourneerd door SEARCH. Het uiteindelijke resultaat is de eerste overeenkomst die in de cel wordt gevonden, niet de eerste overeenkomst die in de lijst met dingen wordt gevonden.
