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.

  • This would be much easier (as well as more intuitive) to do using VBA. – Breakthrough Jan 30 '12 at 12:46
  • Thanks for the suggestion Breakthrough, I've edited the question slightly to allow for this as an option so they can be compared. – Turgs Jan 30 '12 at 13:24

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


For your better understanding, below is the screenshot

enter image description here


Assuming your data starts at A1 try this "array formula" in B2


confirmed with CTRL+SHIFT+ENTER and copied down

or you can use this non-array version


In Excel 2007 or later you can simplify with IFERROR, i.e. array version becomes


For "1st instance" row in column C try this formula in C1 copied down


Note: the formulas don't strictly return "row numbers" but the relative row numbers for your data, i.e. row 1 of your data returns 1 etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.