Generisk formel
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Sammendrag
For å teste en celle for en av flere strenger, og returnere et tilpasset resultat for den første kampen som ble funnet, kan du bruke en INDEKS / MATCH-formel basert på SØK-funksjonen. I eksemplet vist er formelen i C5:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
der ting (E5: E8) og resultater (F5: F8) er kalt områder.
Dette er en matriseformel og må angis med Control + Shift + Enter.
Forklaring
Denne formelen bruker to navngitte områder: ting og resultater . Hvis du porterer denne formelen direkte, må du bruke navngitte områder med samme navn (definert basert på dataene dine). Hvis du ikke vil bruke navngitte områder, bruk absolutt referanser i stedet.
Kjernen i denne formelen er denne kodebiten:
ISNUMBER(SEARCH(things,B5)
Dette er basert på en annen formel (forklart i detalj her) som sjekker en celle for en enkelt understreng. Hvis cellen inneholder undergrunnen, returnerer formelen SANT. Hvis ikke, returnerer formelen FALSE.
Fordi vi gir SEARCH-funksjonen mer enn en ting å se etter, i det nevnte området ting , vil det gi oss mer det ene resultatet, i en matrise som ser slik ut:
(#VALUE!;9;#VALUE!;#VALUE!)
Tall representerer treff i ting , feil representerer gjenstander som ikke ble funnet.
For å forenkle matrisen bruker vi ISNUMBER-funksjonen til å konvertere alle elementene i matrisen til enten SANT eller FALSK. Ethvert gyldig nummer blir SANT, og enhver feil (dvs. en ting ikke funnet) blir FALSE. Resultatet er en matrise som dette:
(FALSE;TRUE;FALSE;FALSE)
som går inn i MATCH-funksjonen som argumentet lookup_array, med en oppslagsverdi på SANT:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH returnerer deretter posisjonen til den første TRUE funnet, 2 i dette tilfellet.
Til slutt bruker vi INDEX funksjon for å hente et resultat fra den navngitte utvalg resultater på den samme posisjonen:
=INDEX(results,2) // returns "found red"
Du kan tilpasse resultatområdet med de verdiene som er fornuftige i ditt brukstilfelle.
Forebygge falske kamper
Et problem med denne tilnærmingen med ISNUMBER + SEARCH-tilnærmingen er at du kan få falske treff fra delvis samsvar i lengre ord. Hvis du for eksempel prøver å matche "dr", kan du også finne "Andrea", "drakk", "drypp" osv. Siden "dr" vises inne i disse ordene. Dette skjer fordi SEARCH automatisk samsvarer med "inneholder-typen".
For en rask løsning kan du pakke søkeord i mellomromstegn (dvs. "dr" eller "dr") for å unngå å finne "dr" i et annet ord. Men dette vil mislykkes hvis "dr" vises først eller sist i en celle.
Hvis du trenger en mer robust løsning, er et alternativ å normalisere teksten først i en hjelpekolonne, og legge til en ledende og etterfølgende plass. Bruk deretter formelen på denne siden på teksten i hjelpekolonnen, i stedet for originalteksten.
