Splošna formula
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Povzetek
Če želite preizkusiti celico za enega od več nizov in vrniti rezultat po meri za prvo najdeno ujemanje, lahko uporabite formulo INDEX / MATCH, ki temelji na funkciji SEARCH. V prikazanem primeru je formula v C5:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kjer so stvari (E5: E8) in rezultati (F5: F8) imenovani obsegi.
To je formula matrike in jo je treba vnesti s tipkama Control + Shift + Enter.
Pojasnilo
Ta formula uporablja dva imenovana obsega: stvari in rezultate . Če neposredno prenašate to formulo, ne pozabite uporabiti imenovanih obsegov z enakimi imeni (opredeljeno na podlagi vaših podatkov). Če ne želite uporabljati imenovanih obsegov, raje uporabite absolutne reference.
Jedro te formule je ta delček:
ISNUMBER(SEARCH(things,B5)
To temelji na drugi formuli (tukaj je podrobno razloženo), ki preverja celico za posamezen podniz. Če celica vsebuje podniz, formula vrne TRUE. V nasprotnem primeru formula vrne FALSE.
Ker dajemo funkciji SEARCH več kot eno stvar, ki jo moramo iskati, nam bo v imenovanem obsegu stvari dala bolj en rezultat v polju, ki je videti takole:
(#VALUE!;9;#VALUE!;#VALUE!)
Številke predstavljajo ujemanja v stvareh , napake predstavljajo elemente, ki niso bili najdeni.
Za poenostavitev matrike s funkcijo ISNUMBER pretvorimo vse elemente v matriko v TRUE ali FALSE. Vsaka veljavna številka postane TRUE in vsaka napaka (tj. Stvar, ki je ni mogoče najti) postane FALSE. Rezultat je matrika, kot je ta:
(FALSE;TRUE;FALSE;FALSE)
ki gre v funkcijo MATCH kot argument lookup_array z lookup_value TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
Nato MATCH vrne položaj prve najdene TRUE, v tem primeru 2.
Končno s funkcijo INDEX pridobimo rezultat iz poimenovanih rezultatov obsega na istem položaju:
=INDEX(results,2) // returns "found red"
Razpon rezultatov lahko prilagodite poljubnim vrednostim, ki so smiselne za vaš primer uporabe.
Preprečevanje lažnih ujemanj
Eden od problemov tega pristopa pri pristopu ISNUMBER + SEARCH je, da lahko v daljših besedah dobite napačna ujemanja iz delnih ujemanj. Če na primer poskušate ujemati "dr", boste morda našli tudi "Andrea", "pili", "kapljali" itd., Ker se v teh besedah pojavlja "dr". To se zgodi, ker SEARCH samodejno izvede ujemanje tipa "vsebuje".
Za hitro rešitev lahko iskalne besede zavijete v presledke (npr. "Dr" ali "dr"), da ne najdete besede "dr" v drugi besedi. Vendar to ne bo uspelo, če se bo v držnici celica prvi ali zadnji pojavil "dr".
Če potrebujete robustnejšo rešitev, je ena možnost besedilo najprej normalizirati v pomožnem stolpcu in dodati vodilni in zadnji prostor. Nato namesto izvirnega besedila uporabite formulo na tej strani besedila v pomožnem stolpcu.
