Общая формула
(=MATCH(FALSE,logical_test,0))
Резюме
Чтобы получить позицию первого совпадения, не содержащего определенного значения, вы можете использовать формулу массива, основанную на функциях ПОИСКПОЗ, ПОИСК и ЕЧИСЛО. В показанном примере формула E5 выглядит так:
(=MATCH(FALSE,data="red",0))
где «данные» - именованный диапазон B5 «B12».
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Объяснение
Эта формула зависит от результата логической проверки ИСТИНА или ЛОЖЬ, где ЛОЖЬ представляет искомое значение. В этом примере логическим тестом является data = "red", введенный как аргумент lookup_array в функции ПОИСКПОЗ:
=MATCH(FALSE,data="red",0)
После запуска тест возвращает массив или значения ИСТИНА и ЛОЖЬ:
=MATCH(FALSE,(TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE),0)
Если для lookup_value установлено значение FALSE, а для match_type установлено значение 0 для принудительного и точного совпадения, функция MATCH возвращает 4, позицию первого FALSE в массиве.
Получить связанное значение
Чтобы получить связанное значение из столбца Quantity, где «количество» - это именованный диапазон C5: C12, вы можете использовать ИНДЕКС и ПОИСКПОЗ вместе:
(=INDEX(quantity,MATCH(FALSE,data="red",0)))
Литерал содержит
Если вам нужно сопоставить первое значение, которое буквально «не содержит», вы можете использовать вариант формулы. Например, чтобы сопоставить первое значение в данных, не содержащее буквы "r", вы можете использовать:
(=MATCH(FALSE,ISNUMBER(SEARCH("r",data)),0))
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Дополнительные сведения об ISNUMBER + SEARCH см. На этой странице.
