Excel: как вернуть последний номер строки, который содержит определенную строку?

Question

В Excel, есть ли формула Excel, которую я могу использовать для заполнения столбца B (в моем примере ниже), который будет возвращать номер последней строки, содержащей определенную строку, перед текущей строкой?

Данные не являются смежными.
Например, данные могут быть:

   |  A (value)    B (prev recent instance)
----------------------------------------------
 1 |  Mike
 2 |  John
 3 |  Mike         1
 4 |  Tony         
 5 |  Mike         3
 6 |  John         2

Затем я хочу расширить его, чтобы столбец C отображал самый ранний экземпляр.

Кажется, очень похоже на этот вопрос:что такое формула Excel, которая будет возвращать мне номер последней строки ячейки в диапазоне, который содержит конкретные данные? Однако этот вопрос и ответ требует, чтобы данные были смежными.

Изменить: я хотел бы, чтобы ответом была формула рабочего листа, однако, если вы считаете, что ее более целесообразно реализовать через VBA, включите ее также в свой ответ с плюсами и минусами каждого подхода.

Answer 1

Предполагая, что ваши данные начинаются с A1, попробуйте эту "формулу массива" в B2

=IF(COUNTIF(A$1:A1,A2),MATCH(2,1/(A$1:A1=A2)),"")

подтверждается комбинациями клавиш CTRL+SHIFT+ENTER и копируется

или вы можете использовать эту версию без массива

=IF(COUNTIF(A$1:A1,A2),MATCH(2,INDEX(1/(A$1:A1=A2),0)),"")

В Excel 2007 или более поздней версии вы можете упростить с помощью IFERROR, то есть версия массива становится

=IFERROR(MATCH(2,1/(A$1:A1=A2)),"")

Для строки «1-й экземпляр» в столбце C попробуйте эту формулу в C1 скопированной

=MATCH(A1,A$1:A1,0)

Примечание: формулы строго не возвращают "номера строк", а относительные номера строк для ваших данных, т.е. строка 1 ваших данных возвращает 1 и т.д.

Answer 2

Чтобы найти самый ранний случай, ниже приводится формула:

=LOOKUP(2,1/(A:A=A2),ROW(A:A))

Для вашего лучшего понимания ниже скриншот