Что нельзя сделать функциями Excel?

Вчера мне предложили решить небольшую задачку для Excel. Вот она:

Имеется таблица:

В ячейку X1 следует поместить наиболее позднее значение из столбца G по указанной дате из B, или "последнее" значение из столбца G.
Просто использовать функцию ВПР нельзя, поскольку дате в B может соответствовать пустое поле в G (желтые строки).

Кроме того, было введено серьезное ограничение: таблица будет просматриваться на коммуникаторе, поэтому использовать VBА или вынести нужный код в отдельную .dll нельзя. Все, что у меня есть - это функции в ячейке.

Обдумывание условий показало, что они неполны.

• Непонятно, откуда должна браться дата для поиска. Функции Excel не распознают ни положения курсора, ни выделения ячеек. Я решил, что дата для поиска будет помещаться в ячейку W1.


• "Последнее" значение в столбце G тоже может оказаться пустым. На этот случай тоже надо было предусмотреть какие-то действия

У меня уже сложился отличный алгоритм. Всего-то делов - начать поиск с последней строки и продвигаться вверх, фильтруя пустые строки. Так, а какая функция это умеет?...

А никакая. Оп-па.

Честно скажу, не ожидал. Но факт есть факт - все функции Excel для поиска в списке могут искать только по возрастающему списку. Они прекращают работу после первого найденного значения - совсем хорошо. Ни циклов, ни рекурсии, ни координат активной ячейки или выделения. Прекрасно.

Ладно, а что же я могу? Могу узнать, существует ли указанная дата. Могу найти значение по дате, хотя бы первое. Могу проверить, не пустое ли оно. Могу вывести результат в X1. Ну так сделаю хотя бы это. Вот что в итоге оказалось в ячейке X1:

=ЕСЛИ(НЕ(ЕНД(ВПР($W$1;$B$1:$G$4096;6;ЛОЖЬ)+ЕПУСТО(ВПР($W$1;$B$1:$G$4096;6;ЛОЖЬ)));ВПР($W$1;$B$1:$G$4096;6;ЛОЖЬ);ЕСЛИ(НЕ(ЕПУСТО(ИНДЕКС($G$1:$G$4096;СЧЕТЗ($В$1:$В$4096))));ИНДЕКС($G$1:$G$4096;СЧЕТЗ($B$1:$B$4096));"---"))

Этот текст функции можно вставлять в ячейку, но понять его нельзя. :) Давайте разберемся, что он делает:

//Если дата, указанная в ячейке W1, имеется в столбце B, и первое значение, соответствующее этой дате, в столбце G - непустое, то

=ЕСЛИ(НЕ(ЕНД(ВПР($W$1;$B$1:$G$4096;6;ЛОЖЬ)+ЕПУСТО(ВПР($W$1;$B$1:$G$4096;6;ЛОЖЬ)));

// Вывести это значение

ВПР($W$1;$B$1:$G$4096;6;ЛОЖЬ);

// Иначе, если последнее значение в столбце G непустое, то

ЕСЛИ(НЕ(ЕПУСТО(ИНДЕКС($G$1:$G$4096;СЧЕТЗ($В$1:$В$4096))));

// Вывести его, иначе вывести прочерк

ИНДЕКС($G$1:$G$4096;СЧЕТЗ($B$1:$B$4096));"---"))

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