Общая формула
(=MATCH(TRUE,IF(LEFT(range,1)"N",TRUE),0))
Резюме
Чтобы сопоставить первое значение, которое не начинается с определенной строки, вы можете использовать массив, основанный на функциях ПОИСКПОЗ и ЛЕВЫЙ. В показанном примере формула в F5:
(=MATCH(TRUE,IF(LEFT(code,1)"N",TRUE),0))
где «код» - именованный диапазон B5: B12.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
Ключом к этой формуле является массив или значения ИСТИНА и ЛОЖЬ, построенные с помощью этого выражения:
LEFT(code,1)"N"
Здесь каждое значение в названном диапазоне «код» оценивается с помощью логической проверки «первая буква не является N». Результатом является массив или значения ИСТИНА и ЛОЖЬ, подобные этому:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE)
Этот массив передается в функцию MATCH как поисковый массив. Значение поиска - ИСТИНА, а тип соответствия устанавливается равным нулю, чтобы обеспечить точное совпадение. Функция ПОИСКПОЗ возвращает позицию первого значения, которое не начинается с буквы «N» (Z09876), то есть 5.
ИНДЕКС и МАТЧ
Чтобы получить значение, связанное с позицией, возвращаемой MATCH, вы можете добавить функцию ИНДЕКС. В показанном примере формула в F6:
(=INDEX(value,MATCH(TRUE,LEFT(code,1)"N",0)))
Обратите внимание, что мы используем ту же формулу MATCH, указанную выше, чтобы предоставить номер строки для INDEX, с массивом, установленным на именованный диапазон «значение». Как и раньше, ПОИСКПОЗ возвращает 5. Затем ИНДЕКС возвращает значение -23 в этой позиции.
Как и раньше, это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
