Обща формула
=LOOKUP(2,1/SEARCH(things,A1),things)
Обобщение
За да проверите клетка за едно от няколко неща и да върнете последното съвпадение, намерено в списъка, можете да използвате формула, базирана на функциите LOOKUP и SEARCH. В случай на намерени множество съвпадения, формулата ще върне последното съвпадение от списъка с „неща“.
В показания пример формулата в C5 е:
=LOOKUP(2,1/SEARCH(things,B5),things)
Обяснение
Контекст: имате списък с неща в посочения диапазон "неща" (E5: E8) и искате да проверите клетките в колона Б, за да видите дали те съдържат тези неща. Ако е така, искате да върнете последния елемент от „нещата“, който е намерен.
В тази формула функцията SEARCH се използва за търсене на клетки в колона Б по следния начин:
SEARCH(things,B5)
Когато SEARCH намери съвпадение, той връща позицията на съвпадението в търсената клетка. Когато търсенето не може да намери съвпадение, връща грешка #VALUE. Тъй като даваме на SEARH повече от едно нещо, което да търси, това ще върне повече от един резултат. В показания пример SEARCH връща масив от резултати като този:
(8;24;#VALUE!;#VALUE!)
След това този масив се използва като делител на числото 1. Резултатът е масив, съставен от грешки и десетични стойности. Грешките представляват неща, които не са намерени, а десетичните стойности представляват намерени неща. В показания пример масивът изглежда така:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Този масив служи като "lookup_vector" за функцията LOOKUP. Справочната стойност се предоставя като число 2, а резултатният вектор е наименованият диапазон "неща". Това е умната част.
Формулата е конструирана по такъв начин, че векторът за справка никога няма да съдържа стойност, по-голяма от 1, докато стойността за справка е 2. Това означава, че стойността за справка никога няма да бъде намерена. В този случай LOOKUP ще съответства на последната числова стойност, намерена в масива, която съответства на последното „нещо“, намерено от SEARCH.
И накрая, използвайки посочения диапазон "неща", предоставени като вектор на резултата, LOOKUP връща последното намерено нещо.
С твърдо кодирани стойности
Използването на диапазон като „неща“ улеснява модифицирането на списъка с думи за търсене (и добавяне на повече думи за търсене), но това не е изискване. Можете също така да твърдо кодирате стойности директно във формулата по следния начин:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
