Generisk formel
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Sammanfattning
För att testa en cell för en av flera strängar och returnera ett anpassat resultat för den första matchningen som hittats kan du använda en INDEX / MATCH-formel baserad på SÖK-funktionen. I exemplet som visas är formeln i C5:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
där saker (E5: E8) och resultat (F5: F8) heter områden.
Detta är en matrisformel och måste anges med Control + Shift + Enter.
Förklaring
Den här formeln använder två namngivna intervall: saker och resultat . Om du överför denna formel direkt, se till att du använder namngivna intervall med samma namn (definierade baserat på dina data). Om du inte vill använda namngivna intervall, använd istället absoluta referenser.
Kärnan i denna formel är detta utdrag:
ISNUMBER(SEARCH(things,B5)
Detta är baserat på en annan formel (förklaras i detalj här) som kontrollerar en cell för en enda substring. Om cellen innehåller understrängen returnerar formeln SANT. Om inte, returnerar formeln FALSE.
Eftersom vi ger SEARCH-funktionen mer än en sak att leta efter, i det nämnda intervallet saker , kommer det att ge oss mer ett resultat, i en matris som ser ut så här:
(#VALUE!;9;#VALUE!;#VALUE!)
Siffror representerar matchningar i saker , fel representerar objekt som inte hittades.
För att förenkla arrayen använder vi ISNUMBER-funktionen för att konvertera alla objekt i arrayen till antingen TRUE eller FALSE. Varje giltigt nummer blir SANT och alla fel (dvs. en sak som inte hittas) blir FALSKA. Resultatet är en array som denna:
(FALSE;TRUE;FALSE;FALSE)
som går in i MATCH-funktionen som argumentet lookup_array, med ett sökvärde på SANT:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH returnerar sedan positionen för den första SANT som hittats, 2 i detta fall.
Slutligen använder vi funktionen INDEX för att hämta ett resultat av det namngivna området resulterar i samma läge:
=INDEX(results,2) // returns "found red"
Du kan anpassa resultatintervallet med de värden som är meningsfulla i ditt användningsfall.
Förhindra falska matchningar
Ett problem med denna metod med ISNUMBER + SEARCH-metoden är att du kan få falska matchningar från partiella matchningar i längre ord. Till exempel, om du försöker matcha "dr" kan du också hitta "Andrea", "drack", "dropp", etc. eftersom "dr" visas inuti dessa ord. Detta händer eftersom SEARCH automatiskt matchar "innehåller-typ".
För en snabb lösning kan du slå sökord i mellanslagstecken (dvs. "dr" eller "dr") för att undvika att hitta "dr" i ett annat ord. Men detta misslyckas om "dr" visas först eller sist i en cell.
Om du behöver en mer robust lösning är ett alternativ att normalisera texten först i en hjälpkolumn och lägga till ett ledande och efterföljande utrymme. Använd sedan formeln på den här sidan på texten i hjälpkolumnen istället för originaltexten.
