Generisk formel
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Resumé
For at teste en celle for en af flere strenge og returnere et brugerdefineret resultat til den første match, kan du bruge en INDEX / MATCH-formel baseret på SEARCH-funktionen. I det viste eksempel er formlen i C5:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
hvor ting (E5: E8) og resultater (F5: F8) er navngivet områder.
Dette er en matrixformel og skal indtastes med Control + Shift + Enter.
Forklaring
Denne formel bruger to navngivne områder: ting og resultater . Hvis du overfører denne formel direkte, skal du sørge for at bruge navngivne områder med de samme navne (defineret ud fra dine data). Hvis du ikke vil bruge navngivne områder, skal du bruge absolutte referencer i stedet.
Kernen i denne formel er dette uddrag:
ISNUMBER(SEARCH(things,B5)
Dette er baseret på en anden formel (forklaret detaljeret her), der kontrollerer en celle for en enkelt substring. Hvis cellen indeholder understrengen, returnerer formlen SAND. Hvis ikke, returnerer formlen FALSK.
Fordi vi giver SEARCH-funktionen mere end én ting at se efter, inden for de navngivne ting , vil det give os mere det ene resultat i en matrix, der ser sådan ud:
(#VALUE!;9;#VALUE!;#VALUE!)
Tal repræsenterer matches i ting , fejl repræsenterer elementer, der ikke blev fundet.
For at forenkle arrayet bruger vi ISNUMBER-funktionen til at konvertere alle elementer i arrayet til enten TRUE eller FALSE. Ethvert gyldigt tal bliver SAND, og enhver fejl (dvs. en ting der ikke findes) bliver FALSK. Resultatet er en matrix som denne:
(FALSE;TRUE;FALSE;FALSE)
der går ind i MATCH-funktionen som argumentet lookup_array med en opslagsværdi på SAND:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH returnerer derefter positionen for den første TRUE fundet, 2 i dette tilfælde.
Endelig bruger vi INDEX-funktionen til at hente et resultat fra det navngivne område resultater på det samme position:
=INDEX(results,2) // returns "found red"
Du kan tilpasse resultatområdet med de værdier, der giver mening i din brugssag.
Forebyggelse af falske kampe
Et problem med denne tilgang med ISNUMBER + SEARCH-metoden er, at du måske får falske matches fra delvise matches i længere ord. For eksempel, hvis du prøver at matche "dr", kan du også finde "Andrea", "drak", "dryp" osv., Da "dr" vises inde i disse ord. Dette sker, fordi SEARCH automatisk matcher en "containertype".
For en hurtig løsning kan du pakke søgeord i mellemrumstegn (dvs. "dr" eller "dr") for at undgå at finde "dr" i et andet ord. Men dette mislykkes, hvis "dr" vises først eller sidst i en celle.
Hvis du har brug for en mere robust løsning, er en mulighed at normalisere teksten først i en hjælpekolonne og tilføje en ledende og efterfølgende plads. Brug derefter formlen på denne side på teksten i hjælpekolonnen i stedet for den originale tekst.
