Генеричка формула
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Резиме
Да бисте тестирали ћелију за један од неколико низова и вратили прилагођени резултат за прво пронађено подударање, можете користити формулу ИНДЕКС / МАТЦХ засновану на функцији СЕАРЦХ. У приказаном примеру, формула у Ц5 је:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
где су ствари (Е5: Е8) и резултати (Ф5: Ф8) именовани опсези.
Ово је формула низа и мора се унети са Цонтрол + Схифт + Ентер.
Објашњење
Ова формула користи два именована опсега: ствари и резултате . Ако преносите ову формулу директно, обавезно користите именоване опсеге са истим именима (дефинисано на основу ваших података). Ако не желите да користите именоване опсеге, уместо тога користите апсолутне референце.
Језгро ове формуле је овај фрагмент:
ISNUMBER(SEARCH(things,B5)
Ово се заснива на другој формули (овде је детаљно објашњено) која проверава да ли ћелија има један подниз. Ако ћелија садржи подниз, формула враћа ТРУЕ. Ако није, формула враћа ФАЛСЕ.
Будући да дајемо функцији СЕАРЦХ више од једне ствари коју треба тражити, у именованим стварима опсега ће нам дати више резултата, у низу који изгледа овако:
(#VALUE!;9;#VALUE!;#VALUE!)
Бројеви представљају подударање у стварима , грешке представљају ставке које нису пронађене.
Да бисмо поједноставили низ, користимо функцију ИСНУМБЕР за претварање свих ставки у низу у ТРУЕ или ФАЛСЕ. Било који важећи број постаје ИСТИНА, а свака грешка (тј. Ствар која није пронађена) постаје ФАЛСЕ. Резултат је низ попут овог:
(FALSE;TRUE;FALSE;FALSE)
који улази у функцију МАТЦХ као аргумент лоокуп_арраи, са лоокуп_валуе од ТРУЕ:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
МАТЦХ затим враћа положај првог пронађеног ТРУЕ, у овом случају 2.
Коначно, користимо функцију ИНДЕКС за дохваћање резултата из именованих резултата опсега на истој позицији:
=INDEX(results,2) // returns "found red"
Можете прилагодити опсег резултата оним вредностима које имају смисла у вашем случају употребе.
Спречавање лажних подударања
Један од проблема овог приступа са приступом ИСНУМБЕР + СЕАРЦХ је тај што можете добити погрешна подударања из делимичних подударања унутар дужих речи. На пример, ако покушате да подударате са „др“, можда ћете пронаћи и „Андреа“, „пити“, „капати“ итд., Јер се „др“ појављује унутар ових речи. То се догађа зато што СЕАРЦХ аутоматски подудара „садржи-тип“.
За брзо решење можете речи за претрагу умотати у размаке (нпр. „Др“ или „др“) како бисте избегли проналазак „др“ у другој речи. Али ово неће успети ако се „др“ појави први или последњи у ћелији.
Ако вам је потребно робусније решење, једна од могућности је да се прво нормализује текст у помоћној колони и дода размак између водећих и пратећих страница. Затим користите формулу на овој страници у тексту у помоћној колони, уместо у оригиналном тексту.
