Excel: How to return the last Row Number that a contains a specific string

microsoft excelworksheet-function

In excel, is there an excel formula I can use to populate Column B (in my example below) that will return the last row number that contains a specific string, before the current row?

The data is not contiguous.
For example, the data may be:

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

I'll then want to expand it to have Column C to show the earliest instance.

It seems very similar to this question:
What is an excel formula that will return me the last row number of a cell within a range that contains specific data?, however that question and answer requires the data to be contiguous.

Edit:
I would like the answer to be a Worksheet Formula, however if you think it's more appropriate to implement via VBA, please include that also in your answer with pros and cons of each approach.

Best Answer

To find earliest instance, mentioned below is the formula:-

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

For your better understanding, below is the screenshot

enter image description here

Related Question