Generisk formel
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Resumé
For at kontrollere en celle for en af flere ting og returnere det første match, der findes på listen, kan du bruge en INDEX / MATCH-formel, der bruger SEARCH eller FIND til at finde et match. I det viste eksempel er formlen i C5:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
hvor "ting" er det navngivne område E5: E9.
Bemærk: dette er en matrixformel og skal indtastes med Control + Shift + Enter.
Forklaring
I dette eksempel har vi en liste over farver i et navngivet område kaldet "ting" (E5: E9). Vi vil kontrollere teksten i kolonne B for at se, om den indeholder nogen af disse farver. I så fald vil vi returnere navnet på den første farve, der blev fundet.
Arbejder indefra og ud, denne formel bruger ISNUMBER-funktionen og SEARCH-funktionen til at søge i teksten i B5 for hver farve, der er anført i "ting" som denne:
ISNUMBER(SEARCH(things,B5)
Dette udtryk er baseret på en formel (forklaret detaljeret her), der kontrollerer en celle for en enkelt understreng. Hvis cellen indeholder understrengen, returnerer udtrykket SAND. Hvis ikke, returnerer udtrykket FALSK.
Når vi giver denne SØGNING, vil en liste over ting (i stedet for en ting) give os en række resultater tilbage. Hver fundet farve genererer en numerisk position, og farver, der ikke findes, genererer en fejl:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
ISNUMBER-funktionen konverterer derefter resultater til SAND / FALSK-værdier. Ethvert tal bliver SAND, og enhver fejl (ikke fundet) bliver FALSK. Resultatet er en matrix som denne:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Denne matrix returneres til MATCH-funktionen som arrayargumentet. Opslagsværdien er SAND, og matchtypen er sat til nul for at tvinge et nøjagtigt match. Når der er en matchende farve, returnerer MATCH den position, der først blev fundet SAND. Denne værdi føres ind i INDEX-funktionen som række nummer, med det navngivne interval "ting" angivet som array. Når der er mindst et match, returnerer INDEX farven på den position. Når der ikke findes noget match, returnerer denne formel fejlen # N / A.
Med hårdkodede værdier
Hvis du ikke ønsker at oprette et eksternt navngivet område som "ting" i dette eksempel, kan du hårdkode værdier i formlen som "matrixkonstanter" som denne:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Få første kamp i cellen
Sprog her er ret forvirrende, men formlen ovenfor returnerer den første kamp, der findes i listen over ting, du skal kigge efter. Hvis du i stedet vil returnere det første match, der findes i cellen, der testes, kan du prøve en formel som denne:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
I denne version af formlen er MATCH-funktionen indstillet til at lede efter resultatet af dette uddrag:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
som bruger AGGREGATE-funktionen til at få minimumværdien i de resultater, der returneres af SEARCH. Vi har brug for AGGREGATE her, fordi det indgående array sandsynligvis vil indeholde fejl (returneres af SEARCH når ting ikke findes), og vi har brug for en funktion, der ignorerer disse fejl og stadig giver os den mindste numeriske værdi.
Resultatet fra AGGREGATE returneres direkte til MATCH som opslagsværdi sammen med det samme array returneret af SEARCH. Det endelige resultat er den første kamp, der findes i cellen, ikke den første kamp, der findes i listen over ting.
