Найти значение по условиям в диапазоне ячеек

[Максим Арцимович]

Добрый день! Помогите пожалуйста с формулой.Нужно получить значение ячейки, из желтого диапазона, в которой первой выполняется одно из условий: значение в столбце С  больше А1, значение в столбце D  меньше В1

[cheshiki1]

почему у вас результат 2 написан? (или это просто так)
проверяйте, формула массива

Код Выделить Развернуть

=ПОИСКПОЗ(1;(C2:C7>A1)*(D2:D7<B1);)
в версии 2007 и выше есть ф-ция СЧЕТЕСЛИМН

[Максим Арцимович]

результат 2 написан потому что, 2 первым выполнено условие -значение в столбце D  меньше В1. Наверно я задачу не понятно написал..

[cheshiki1]

Код Выделить Развернуть

=ИНДЕКС($C$2:$D$7;МИН(ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C1:C6);""));ЕСЛИ(МИН(ЕСЛИ(C2:C7>A1;СТРОКА(C1:C6);""))>МИН(ЕСЛИ(D2:D7<B1;СТРОКА(C1:C6);""));2;1))
формула массива

[Максим Арцимович]

Спасибо огромное ), теперь все в порядке. Если не сложно, объясните, для чего  используется СТРОКА(C1:C6)

[cheshiki1]

привязка условий к номеру строки в которой оно выполнилось.

Код Выделить Развернуть

ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C1:C6);"")=ЕСЛИ(({ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА})+(D2:D7<B1);СТРОКА(C1:C6);"")=ЕСЛИ(({ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА})+({ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ});СТРОКА(C1:C6);"")=ЕСЛИ({0:0:1:1:2:1};СТРОКА(C1:C6);"")=ЕСЛИ({0:0:1:1:2:1};{1:2:3:4:5:6};"")={"":"":3:4:5:6}
расписал как пошагово работает часть формулы.

[Максим Арцимович]

спасибо. А если диапазон поиска, сдвинется вниз( типа C3:D8 ) или сузится (типа C5:D7) как изменять СТРОКА(C1:C6), относительно нового диапазона?, больше ничего менять не нужно в формуле -верно?... или это я не корректно спрашиваю?

[cheshiki1]

кол-во строк должно равняться к-ву строк в таблице и обязательно с первой строки.
C5:D7=3строки соответственно СТРОКА(C1:C3) (буква здесь не важна), если формула будет растягиваться по ячейкам то диапазон нужно закрепить (читаем про абсолютные и относительные ссылки).

[Максим Арцимович]

перебирал значения -вот вариант, где не правильно работает формула . В диапазоне будут  только числа, положительные

[vikttur]

формула массива:

Код Выделить Развернуть

=ИНДЕКС(C2:C7;МИН(ЕСЛИ(C2:C7>A1;ЕСЛИ(D2:D7<B1;СТРОКА(C2:C7)-1))))
В Ваших данных нет подходящего числа.

[Максим Арцимович]

Вы смотрели файл Z_123_1 ? там одно решение в D6 =2, наверно я  с условиями не точен? Нужно получить значение ячейки, из желтого диапазона, в которой первой выполняется  ЛЮБОЕ из условий
значение в столбце С  больше А1
значение в столбце D  меньше В1

[vikttur]

Предыдущая объединяет условия.
Для варианта "любое из условий" формула массива:

Код Выделить Развернуть

=ИНДЕКС(C2:D7;МИН(ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C2:C7)-1));(B1>ИНДЕКС(D2:D7;МИН(ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C2:C7)-1))))+1)
Как видно, определение строки повторяется два раза. Для оптимизации эту часть лучше вынести в отдельную ячейку.
Сравните с предыдущей формулой. формула массива (ФМ):

Код Выделить Развернуть

=МИН(ЕСЛИ((C2:C7>A1)+(D2:D7<B1);СТРОКА(C2:C7)-1))
Обычная:

Код Выделить Развернуть

=ИНДЕКС(C2:D7;ФМ;(B1>ИНДЕКС(D2:D7;ФМ))+1)
ФМ - ссылка на ячейку с ФМ