Генеричка формула
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Резиме
Да бисте проверили у ћелији једну од неколико ствари и вратили прво подударање нађено на листи, можете користити формулу ИНДЕКС / МАТЦХ која користи СЕАРЦХ или ФИНД за проналажење подударања. У приказаном примеру, формула у Ц5 је:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
при чему је „ствари“ именовани опсег Е5: Е9.
Напомена: ово је формула низа и мора се унети са Цонтрол + Схифт + Ентер.
Објашњење
У овом примеру имамо списак боја у именованом опсегу под називом „ствари“ (Е5: Е9). Желимо да проверимо текст у колони Б да бисмо видели да ли садржи неку од ових боја. Ако је то случај, желимо да вратимо име прве пронађене боје.
Радећи изнутра, ова формула користи функцију ИСНУМБЕР и СЕАРЦХ како би претражила текст у Б5 за сваку боју наведену у „стварима“ попут ове:
ISNUMBER(SEARCH(things,B5)
Овај израз заснован је на формули (која је овде детаљно објашњена) која проверава да ли ћелија има један подниз. Ако ћелија садржи подниз, израз враћа ТРУЕ. Ако није, израз враћа ФАЛСЕ.
Када овом ПРЕТРАЖИВАЊУ дамо листу ствари (уместо једне ствари), вратиће нам низ резултата. Свака пронађена боја генерираће нумерички положај, а боје које нису пронађене генерираће грешку:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Функција ИСНУМБЕР затим претвара резултате у вредности ТРУЕ / ФАЛСЕ. Било који број постаје ИСТИНА, а свака грешка (која није пронађена) постаје ФАЛСЕ. Резултат је низ попут овог:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Овај низ се враћа функцији МАТЦХ као аргумент низа. Вредност тражења је ТРУЕ, а тип подударања је постављен на нулу да би се форсирало тачно подударање. Када постоји одговарајућа боја, МАТЦХ враћа позицију прва пронађена ТРУЕ. Ова вредност се уводи у функцију ИНДЕКС као број реда, а именовани опсег „ствари“ се даје као низ. Када постоји бар једно подударање, ИНДЕКС враћа боју на тај положај. Када се не пронађе подударање, ова формула враћа грешку # Н / А.
Са тешко кодираним вредностима
Ако не желите да поставите спољни именовани опсег попут „ствари“ у овом примеру, можете тврдо кодирати вредности у формулу као „константе низа“ овако:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Добијте прву утакмицу у ћелији
Језик овде је прилично збуњујући, али горња формула ће вратити прво подударање нађено на списку ствари које треба тражити. Ако уместо тога желите да вратите прво подударање пронађено у ћелији која се тестира, можете испробати формулу попут ове:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
У овој верзији формуле, функција МАТЦХ је постављена да тражи резултат овог исечка:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
који користи функцију АГРЕГАТЕ да добије минималну вредност у резултатима које враћа СЕАРЦХ. Овде нам је потребан АГРЕГАТЕ, јер ће долазни низ вероватно садржати грешке (враћене СЕАРЦХ-ом када ствари нису пронађене), а потребна нам је функција која ће те грешке игнорисати и још увек нам дати минималну нумеричку вредност.
Резултат из АГРЕГАТЕ враћа се директно у МАТЦХ као вредност претраживања, заједно са истим низом који враћа СЕАРЦХ. Коначни резултат је прво пронађено подударање у ћелији, а не прво пронађено на списку ствари.
