Generisk formel
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Sammendrag
For å sjekke en celle for en av flere ting, og returnere den første kampen som finnes i listen, kan du bruke en INDEKS / MATCH-formel som bruker SØK eller FINN for å finne en kamp. I eksemplet vist er formelen i C5:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
der "ting" er det navngitte området E5: E9.
Merk: dette er en matriseformel og må angis med Control + Shift + Enter.
Forklaring
I dette eksemplet har vi en liste over farger i et navngitt utvalg kalt "ting" (E5: E9). Vi vil sjekke teksten i kolonne B for å se om den inneholder noen av disse fargene. I så fall vil vi returnere navnet på den første fargen som ble funnet.
Denne formelen arbeider fra innsiden og ut og bruker ISNUMBER-funksjonen og SEARCH-funksjonen for å søke i teksten i B5 for hver farge som er oppført i "ting" som dette:
ISNUMBER(SEARCH(things,B5)
Dette uttrykket er basert på en formel (forklart i detalj her) som sjekker en celle for en enkelt understreng. Hvis cellen inneholder undergrunnen, returnerer uttrykket SANT. Hvis ikke, returnerer uttrykket FALSE.
Når vi gir dette SØK, vil en liste over ting (i stedet for en ting) gi oss en rekke resultater. Hver farge som er funnet vil generere en numerisk posisjon, og farger som ikke blir funnet, vil generere en feil:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
ISNUMBER-funksjonen konverterer deretter resultatene til SANNE / FALSE verdier. Ethvert tall blir SANT, og eventuelle feil (ikke funnet) blir FALSE. Resultatet er en matrise som dette:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Denne matrisen returneres til MATCH-funksjonen som arrayargumentet. Oppslagsverdien er SANT og samsvarstypen er satt til null for å tvinge en eksakt samsvar. Når det er en matchende farge, returnerer MATCH posisjonen først TRUE funnet. Denne verdien blir matet inn i INDEX-funksjonen som radnummer, med det nevnte området "ting" gitt som matrise. Når det er minst en kamp, returnerer INDEX fargen på den posisjonen. Når det ikke blir funnet samsvar, returnerer denne formelen feilen # ikke relevant.
Med hardkodede verdier
Hvis du ikke vil sette opp et eksternt navngitt område som "ting" i dette eksemplet, kan du hardkode verdier i formelen som "matrisekonstanter" slik:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Få første kamp i cellen
Språket her er ganske forvirrende, men formelen ovenfor vil returnere den første kampen som finnes i listen over ting du skal se etter. Hvis du i stedet vil returnere den første kampen som ble funnet i cellen som 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 versjonen av formelen er MATCH-funksjonen satt opp for å se etter resultatet av denne utdraget:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
som bruker AGGREGATE-funksjonen for å få minimumsverdien i resultatene returnert av SØK. Vi trenger AGGREGATE her, fordi den innkommende matrisen sannsynligvis vil inneholde feil (returnert av SØK når ting ikke blir funnet), og vi trenger en funksjon som vil ignorere disse feilene og likevel gi oss den minste numeriske verdien.
Resultatet fra AGGREGATE returneres direkte til MATCH som oppslagsverdi, sammen med samme matrise returnert av SEARCH. Det endelige resultatet er den første kampen i cellen, ikke den første kampen som finnes i listen over ting.
