Generisk formel
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Sammanfattning
För att kontrollera en cell för en av flera saker och returnera den första matchningen som hittades i listan kan du använda en INDEX / MATCH-formel som använder SÖK eller HITTA för att hitta en matchning. I exemplet som visas är formeln i C5:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
där "saker" är namngivna intervall E5: E9.
Obs: detta är en matrisformel och måste anges med Control + Shift + Enter.
Förklaring
I det här exemplet har vi en lista med färger i ett namngivet intervall som heter "saker" (E5: E9). Vi vill kontrollera texten i kolumn B för att se om den innehåller någon av dessa färger. I så fall vill vi returnera namnet på den första färgen som hittades.
Arbetar inifrån och ut, den här formeln använder ISNUMBER-funktionen och SEARCH-funktionen för att söka i texten i B5 för varje färg som listas i "saker" så här:
ISNUMBER(SEARCH(things,B5)
Detta uttryck är baserat på en formel (förklaras i detalj här) som kontrollerar en cell för en enda substring. Om cellen innehåller understrängen returnerar uttrycket SANT. Om inte, returnerar uttrycket FALSE.
När vi ger den här SÖKNINGEN en lista över saker (i stället för en sak) ger oss en rad resultat. Varje färg som hittas genererar en numerisk position och färger som inte hittas genererar ett fel:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
ISNUMBER-funktionen konverterar sedan resultat till SANT / FALSKA värden. Varje nummer blir SANT och alla fel (hittades inte) blir FALSE. Resultatet är en array som denna:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Denna matris returneras till MATCH-funktionen som arrayargument. Uppslagsvärdet är SANT och matchningstypen är noll för att tvinga en exakt matchning. När det finns en matchande färg returnerar MATCH den position som SANNA hittades först. Detta värde matas in i INDEX-funktionen som radnummer, med det angivna intervallet "saker" som matris. När det finns minst en matchning returnerar INDEX färgen på den positionen. När ingen matchning hittas returnerar denna formel # N / A-felet.
Med hårdkodade värden
Om du inte vill ställa in ett externt namngivet intervall som "saker" i det här exemplet kan du hårdkoda värden i formeln som "arraykonstanter" så här:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Få första matchen i cellen
Språket här är ganska förvirrande, men formeln ovan returnerar den första matchningen som finns i listan över saker att leta efter. Om du istället vill returnera den första matchningen som hittades i cellen som testas, kan du prova en sådan formel:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
I den här versionen av formeln är MATCH-funktionen inställd för att leta efter resultatet av detta utdrag:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
som använder AGGREGATE-funktionen för att få minimivärdet i de resultat som returneras av SÖK. Vi behöver AGGREGATE här, eftersom den inkommande matrisen sannolikt kommer att innehålla fel (returneras av SÖK när saker inte hittas), och vi behöver en funktion som ignorerar dessa fel och ändå ger oss det minsta numeriska värdet.
Resultatet från AGGREGATE returneras direkt till MATCH som uppslagsvärde, tillsammans med samma array returnerad av SEARCH. Det slutliga resultatet är den första matchningen som hittas i cellen, inte den första matchningen som finns i listan över saker.
