Обща формула
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Обобщение
За да проверите клетка за едно от няколко неща и да върнете първото съвпадение, намерено в списъка, можете да използвате формула INDEX / MATCH, която използва SEARCH или FIND, за да намерите съвпадение. В показания пример формулата в C5 е:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
където "неща" е наименуваният диапазон E5: E9.
Забележка: това е формула на масив и трябва да се въведе с Control + Shift + Enter.
Обяснение
В този пример имаме списък с цветове в определен диапазон, наречен "неща" (E5: E9). Искаме да проверим текста в колона Б, за да видим дали съдържа някой от тези цветове. Ако е така, искаме да върнем името на първия намерен цвят.
Работейки отвътре навън, тази формула използва функцията ISNUMBER и функцията SEARCH за търсене в текста в B5 за всеки цвят, изброен в "неща" по следния начин:
ISNUMBER(SEARCH(things,B5)
Този израз се основава на формула (обяснена подробно тук), която проверява клетката за единичен подниз. Ако клетката съдържа подниза, изразът връща TRUE. Ако не, изразът връща FALSE.
Когато дадем на това ТЪРСЕНЕ списък с неща (вместо едно нещо) ще ни върне масив от резултати. Всеки намерен цвят ще генерира числово положение, а цветовете, които не са намерени, ще генерират грешка:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
След това функцията ISNUMBER преобразува резултатите в TRUE / FALSE стойности. Всяко число става ИСТИНСКО, а всяка грешка (не е намерена) се превръща в FALSE. Резултатът е масив като този:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Този масив се връща към функцията MATCH като аргумент на масива. Стойността за търсене е TRUE и типът на съвпадението е зададен на нула, за да се принуди точно съвпадение. Когато има съвпадащ цвят, MATCH връща позицията първа намерена TRUE. Тази стойност се подава във функцията INDEX като номер на реда, като посоченият диапазон "неща" се предоставя като масив. Когато има поне едно съвпадение, INDEX връща цвета в тази позиция. Когато не бъде намерено съвпадение, тази формула връща грешка # N / A.
С твърдо кодирани стойности
Ако не искате да настроите външен именуван диапазон като "неща" в този пример, можете да въведете стойности във формулата като "константи на масива" по следния начин:
(=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))
В тази версия на формулата функцията MATCH е настроена да търси резултата от този фрагмент:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
който използва функцията AGGREGATE, за да получи минималната стойност в резултатите, върнати от SEARCH. Тук се нуждаем от AGGREGATE, тъй като входящият масив вероятно ще съдържа грешки (върнати от SEARCH, когато нещата не са намерени), а ние се нуждаем от функция, която ще игнорира тези грешки и пак ще ни даде минималната числова стойност.
Резултатът от AGGREGATE се връща директно в MATCH като справочна стойност, заедно със същия масив, върнат от SEARCH. Крайният резултат е първото съвпадение, намерено в клетката, а не първото съвпадение, намерено в списъка с неща.
