Обща формула
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Обобщение
За да тествате клетка за един от няколко низа и да върнете персонализиран резултат за първото намерено съвпадение, можете да използвате формула INDEX / MATCH, базирана на функцията SEARCH. В показания пример формулата в C5 е:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
където нещата (E5: E8) и резултатите (F5: F8) са наречени диапазони.
Това е формула на масив и трябва да се въведе с Control + Shift + Enter.
Обяснение
Тази формула използва два именувани диапазона: неща и резултати . Ако пренасяте директно тази формула, не забравяйте да използвате именувани диапазони със същите имена (дефинирани въз основа на вашите данни). Ако не искате да използвате именувани диапазони, вместо това използвайте абсолютни препратки.
Ядрото на тази формула е този фрагмент:
ISNUMBER(SEARCH(things,B5)
Това се основава на друга формула (обяснена подробно тук), която проверява клетката за единичен подниз. Ако клетката съдържа подниза, формулата връща TRUE. Ако не, формулата връща FALSE.
Тъй като ние даваме на функцията SEARCH повече от едно нещо, което да търсим, в посочените неща от диапазона тя ще ни даде повече единния резултат в масив, който изглежда така:
(#VALUE!;9;#VALUE!;#VALUE!)
Числата представляват съвпадения в нещата , грешките представляват елементи, които не са намерени.
За да опростим масива, използваме функцията ISNUMBER, за да преобразуваме всички елементи в масива в TRUE или FALSE. Всеки валиден номер става ИСТИНСКИ, а всяка грешка (т.е. нещо, което не е намерено) се превръща в FALSE. Резултатът е масив като този:
(FALSE;TRUE;FALSE;FALSE)
което влиза във функцията MATCH като аргумент lookup_array, с lookup_value на TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
След това MATCH връща позицията на първото намерено TRUE, в този случай 2.
И накрая, ние използваме функцията INDEX за извличане на резултат от наименувания диапазон резултатите в същата позиция:
=INDEX(results,2) // returns "found red"
Можете да персонализирате диапазона с резултатите според стойностите, които имат смисъл във вашия случай на употреба.
Предотвратяване на фалшиви съвпадения
Един проблем с този подход с подхода ISNUMBER + SEARCH е, че може да получите неверни съвпадения от частични съвпадения в по-дълги думи. Например, ако се опитате да съчетаете "dr", можете също да намерите "Andrea", "изпил", "капка" и т.н., тъй като "dr" се появява вътре в тези думи. Това се случва, тъй като SEARCH автоматично прави съвпадение „тип тип“.
За бързо решение можете да увиете думите за търсене в интервали (т.е. „dr“ или „dr“), за да избегнете намирането на „dr“ в друга дума. Но това ще се провали, ако „dr“ се появи първо или последно в клетка.
Ако се нуждаете от по-надеждно решение, една от опциите е да нормализирате текста първо в помощна колона и да добавите водещо и последващо пространство. След това използвайте формулата на тази страница върху текста в помощната колона, вместо оригиналния текст.
