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

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:-

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

For your better understanding, below is the screenshot Assuming your data starts at A1 try this "array formula" in B2

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

confirmed with CTRL+SHIFT+ENTER and copied down

or you can use this non-array version

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

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

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

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

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

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.