Как я получаю ссылку на последнее вхождение значения в массиве в Excel?

Я предполагаю, что A1:A5 отсортированы в порядке возрастания. Если это так, можно использовать следующую формулу:

=ADDRESS(ROW(A1)-1+MATCH(4,A1:A5,1),COLUMN(A1))

Это даст вывод 4$A$.

Вот то, как это работает:

MATCH(4,A1:A5,1) находит индекс самого большого значения, которое является <= 4, предполагая, что A1:A5 отсортирован в порядке возрастания. То, что это действительно означает, - то, что это находит первое значение больше, чем 4 и просто возвращает индекс перед тем индексом.

ADDRESS(row,col) преобразовывает номер строки и номер столбца в ссылку на ячейку. Для номера столбца я просто использовал столбец списка: COLUMN(A1). Для номера строки я использовал индекс, возвращенный из MATCH функционируйте как смещение с начала списка (ROW(A1)-1). Вы могли опустить ROW(A1)-1 и это все еще работало бы в этом случае, но это перестанет работать как только Ваш список, запущенный где-нибудь кроме строки 1.

Обратите внимание, что для использования этого ссылочного значения где-то в другом месте необходимо будет использовать: INDIRECT(B1).

Вы могли использовать формулу массива для нахождения МАКСА строк

{=INDEX(A1:A5,MAX((A1:A5=4)*(ROW(A1:A5))),1)}

Войдите с control+shift+enter. МАКС. часть возвратит самый большой номер строки всех ячеек, которые содержат '4'. Это может быть всем, в чем Вы нуждаетесь, но я перенес это в ИНДЕКСНУЮ функцию, которая указывает на ячейку в случае, если Вам нужен тот дополнительный шаг.

Существует объяснение здесь (поиск "F94"), но быть предупрежденным, это не симпатично!