Generieke formule
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Samenvatting
Als u een cel op een van de verschillende tekenreeksen wilt testen en een aangepast resultaat wilt retourneren voor de eerste gevonden overeenkomst, kunt u een INDEX / MATCH-formule gebruiken op basis van de functie SEARCH. In het getoonde voorbeeld is de formule in C5:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
waar dingen (E5: E8) en resultaten (F5: F8) bereiken worden genoemd.
Dit is een matrixformule en moet worden ingevoerd met Control + Shift + Enter.
Uitleg
Deze formule gebruikt twee benoemde bereiken: dingen en resultaten . Als u deze formule rechtstreeks port, zorg er dan voor dat u benoemde bereiken met dezelfde naam gebruikt (gedefinieerd op basis van uw gegevens). Als u geen benoemde bereiken wilt gebruiken, gebruikt u in plaats daarvan absolute verwijzingen.
De kern van deze formule is dit fragment:
ISNUMBER(SEARCH(things,B5)
Dit is gebaseerd op een andere formule (hier in detail uitgelegd) die een cel controleert op een enkele subtekenreeks. Als de cel de subtekenreeks bevat, retourneert de formule WAAR. Als dit niet het geval is, retourneert de formule FALSE.
Omdat we de SEARCH-functie meer dan één ding geven om naar te zoeken, in de genoemde reeks dingen , zal het ons meer het ene resultaat geven, in een array die er als volgt uitziet:
(#VALUE!;9;#VALUE!;#VALUE!)
Cijfers vertegenwoordigen overeenkomsten in dingen , fouten vertegenwoordigen items die niet zijn gevonden.
Om de array te vereenvoudigen, gebruiken we de functie ISNUMBER om alle items in de array naar TRUE of FALSE te converteren. Elk geldig nummer wordt WAAR, en elke fout (dwz iets dat niet wordt gevonden) wordt ONWAAR. Het resultaat is een array als deze:
(FALSE;TRUE;FALSE;FALSE)
die in de MATCH-functie gaat als het lookup_array-argument, met een lookup_value van TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
VERGELIJKEN retourneert dan de positie van de eerste gevonden WAAR, 2 in dit geval.
Tenslotte gebruiken we de functie INDEX om een resultaat te halen uit het benoemde bereik resultaten op die dezelfde positie:
=INDEX(results,2) // returns "found red"
U kunt het resultatenbereik aanpassen met de waarden die in uw gebruikssituatie zinvol zijn.
Voorkomen van valse overeenkomsten
Een probleem met deze benadering met de ISNUMBER + SEARCH-benadering is dat u mogelijk valse overeenkomsten krijgt door gedeeltelijke overeenkomsten binnen langere woorden. Als u bijvoorbeeld "dr" probeert te matchen, kunt u ook "Andrea", "drank", "infuus", enz. Vinden, aangezien "dr" in deze woorden voorkomt. Dit gebeurt omdat SEARCH automatisch een "bevat-type" -overeenkomst doet.
Voor een snelle oplossing kunt u zoekwoorden in spaties plaatsen (bijv. "Dr" of "dr") om te voorkomen dat u "dr" in een ander woord vindt. Maar dit zal mislukken als "dr" als eerste of laatste in een cel verschijnt.
Als u een robuustere oplossing nodig heeft, is een optie om de tekst eerst in een hulpkolom te normaliseren en een voorloop- en volgspatie toe te voegen. Gebruik dan de formule op deze pagina voor de tekst in de hulpkolom, in plaats van de originele tekst.
