Поиск первого совпадения, которое не содержит

Чтобы найти позицию первого совпадения, не содержащего заданную строку, используйте формулу на основе функций ПОИСКПОЗ, ЕЧИСЛО и ПОИСК.

Как найти первое совпадение, не содержащее строку «TOOL»?

Чтобы найти позицию первого значения, не содержащего строку «TOOL» в приведённом выше списке, используйте функции ПОИСК и ЕЧИСЛО для создания массива значений ИСТИНА и ЛОЖЬ: ИСТИНА означает, что значение содержит строку «TOOL», а ЛОЖЬ — что не содержит. Затем функция ПОИСКПОЗ определит первую позицию с ЛОЖЬ и вернёт номер первой ячейки, не содержащей «TOOL».

Общий синтаксис

=MATCH(FALSE,ISNUMBER(SEARCH(lookup_value,range)),0)

√ Примечание: Это формула массива, для ввода которой необходимо нажать Ctrl+Shift+Enter.

Чтобы найти позицию первого значения, не содержащего строку «TOOL», скопируйте или введите приведённую ниже формулу в ячейку E5 и нажмите Ctrl+Shift+Enter, чтобы получить результат:

=ПОИСКПОЗ(ЛОЖЬ;ЕЧИСЛО(ПОИСК()))«TOOL»;B5:B11));0)

Либо используйте Ссылка на ячейку, чтобы сделать формулу динамической:

=ПОИСКПОЗ(ЛОЖЬ;ЕЧИСЛО(ПОИСК()))E4;B5:B11));0)

Пояснение формулы

=MATCH(FALSE,(ISNUMBER(SEARCH(E4,B5:B11)),0)

• SEARCH(E4,B5:B11):Функция ПОИСК ищет строку TOOL(значение в ячейке)E4) в каждом значении диапазона B5:B11 и возвращает номера начальных позиций этой строки в этих значениях в виде массива: {3;3;4;#ЗНАЧ!;1;3;#ЗНАЧ!}.
• ЕЧИСЛО()SEARCH(E4,B5:B11))=ЕЧИСЛО(){3;3;4;#ЗНАЧ!;1;3;#ЗНАЧ!}):Функция ЕЧИСЛО преобразует числовые значения в массиве в ИСТИНА, а все остальные — в ЛОЖЬ. В результате получаем:{ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}.
• ПОИСКПОЗ(ЛОЖЬ,())ЕЧИСЛО()SEARCH(E4,B5:B11)),0) = ПОИСКПОЗ(ЛОЖЬ,(){ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ},0):Параметр тип_сопоставления 0 заставляет функцию ПОИСКПОЗ выполнять точное сопоставление. Затем функция возвращает позицию первого значения ЛОЖЬ в массиве. Поэтому функция вернёт 4, поскольку первое значение ЛОЖЬ находится на 4-й позиции.

Важно знать

• Формула не учитывает регистр. Чтобы выполнить сопоставление с учётом регистра, ознакомьтесь с инструкцией здесь.
• Чтобы получить значение по позиции, возвращённой функцией ПОИСКПОЗ, используйте функцию ИНДЕКС.

Связанные функции

Связанные формулы